Power Query: Power Query Online – Part 9
4 October 2023
Welcome to our Power Query blog. Today, I look at the use of keys in Power Query Online.
In the current series, I am looking at Power Query Online, which I have accessed from Power Apps:
Last week, I looked at the functionality on the ‘Schema tools’ tab, which allowed me to set a column as a key.
I chose to do this for Country Code:
To see how keys can be useful in making processes more efficient, I note how long it takes for me to refresh the Merge query that I created in part 7:
The key symbol now appears next to the Country Code column. The key icons make it much easier to see where the keys are when I am merging data. Note the time at the bottom-left of the screen:
On the query Population Data, I mark the column 2-alpha code as a key, using the option on the ‘Schema tools’ tab in the schema view. This was the column I selected in Population Data when I merged it with Region Data.
Then, in the query Region Data, I again mark 2-alpha code as a key:
Now, I go back to the data view, and refresh Merge again:
Notice first that the column 2-alpha code on the merged table is now marked as a key (since it came from Population Data). The time has improved:
Ideally all the keys should be numeric, as these are more efficient to store, but this simple example has shown that marking the correct columns as keys can help when loading data. That’s it for this week; next time, I will look at how I can use the diagram view when analysing my queries.
Come back next time for more ways to use Power Query!