Do you know how powerful lists are in Power Query? You may not realize how often they are used. Most of the time they are disguised as ugly texts within ugly curly brackets. If you've renamed columns, rearranged columns, extracted columns, or replaced values within columns, then you've used lists!
What are lists?
Lists are containers of data. Numbers, texts, dates... anything. Lists always begin with an open curly bracket "{" and end with a closed curly bracket "}". Each value in the list is separated with a comma. Here's an example:
= {"Hello", "World"}This will give you the following result in Power Query:
Dynamically Reordering Columns
1) How does this help us? Let's say you want to dynamically rearrange the column order in your data set. To do this normally, you would just select and drag the columns in the appropriate order.
Wait, did you see that? There it is! A list!
= Table.ReorderColumns(
#"Renamed Columns",
{"Date", "Qty", "Product"}
)This is easy for small tables with only a few columns. But what if you had a table with 50 columns, or 200 columns!? Talk about a drag!
2) To make this process easier on our lives, we can set up our own list. I will start by creating a table directly within Power Query, but this can be any source that you want (like an Excel spreadsheet, or database in the cloud, etc.). This is just one column with the appropriate sort order.
3) From here, we just need to convert this to a list. Power Query makes this really easy for us with the "Convert to List" function.
Notice how the column header now says "List".
4) Now we just need to replace the raw list we saw before, with a reference to this list we just created.
= Table.ReorderColumns(#"Changed Type",#"Column Sort Order")And you're done!
Nested Lists
Let's say you want to dynamically rename your columns. Your boss wants you to rename these column headers as follows:
Date -> Date Sold
Product -> Product Line
Qty -> Quantity Sold
(In fact this method we are about to go over is a good way to prevent the all-to-common "Column Name Not Found" error.)
1) To do this normally, we would simply double click the header names and rename.
Like a dream within a dream, these are lists within lists. A "nested" list, if you will. Notice there are curly brackets enclosing each of the 3 lists here.
{{"Date", "Date Sold"},{"Product","Product Line"},{"Qty","Quantity Sold"}}2) To dynamically accomplish this, we need to set up another table like we did before with reordering the columns. Again, I'll do this directly in Power Query, this time creating 2 columns. Note the column order matters here!
3) Because this is a list within a list, we aren't able to leverage the quick and easy "Convert to List" button. Fortunately, the solution is just as easy. We just need to wrap the Table.ToRows() function around our last step.
4) And now we just need to replace the original list with our list we just made here.
These were just a couple ways you can use lists to make your Power Query much more dynamic. Cheers!
