vurexcellent.blogg.se - Import json into excel 2013

(Am I the only person that doesn’t like this new dialog? I thought the old checkboxes were much simpler, although I do appreciate the new flexibility on where to put your Excel table output) button to close the Query Editor, and then choose the Only Create Connection option to make sure the output of the query is not loaded anywhere and the query is disabled, then click the Load button. There’s a bit more information on this subject in my Power Query book, which I hope you have all bought! Yes, tables in Power Query can have primary keys – the Table.AddKey() function is another way of doing this. …and one of the side-effects of using Table.Distinct() is that it adds a primary key to the table. Why are we doing this when there clearly aren’t any duplicate values in this column? The new step contains the expression

When the Query Editor window opens, right click on the FruitID column and select Remove Duplicates.

Click inside the Dimension table and then, on the Power Query tab in the Excel ribbon, click the From Table button to create a new query.

Here are the steps to use Power Query to create the relationship automatically: If you were to load these two tables into the Excel Data Model, you would probably want to create a relationship between the two tables based on the FruitID column. That said, I think we’re stuck with the current behaviour (at least for the time being) so I thought I might as well document it.Ĭonsider the following Excel worksheet with two tables in it, called Dimension and Fact: You have to follow some very precise steps to make it happen and even then there are some problems. I’ve known about this for a while but not blogged about it because I don’t think it works all that well.

But did you know that you can get Power Query to do this for other data sources too? You probably know that, when you are importing data from multiple tables in SQL Server into the Excel Data Model in Excel 2013 using Power Query, Power Query will automatically create relationships between those tables in the Data Model.