Power BI / query, select columns dynamically

Question

For some reason I need to extract a table with selecting columns in power query dynamically.

For example,

In query 1, I have Columns ("ID", "column1", "column2","column3","column4", "column5", "column6")

In query 2, this is a list from another source that contains a list of column labels i need, eg. ("column1", "column5", "column6") <--- String

So i want to have columns ("ID", "column1", "column5", "column6") in my final table.

Is there any good suggestion for this?

Marc Pincince Marc Pincince · Accepted Answer · 2018-05-28T15:22:26

Here's a possible solution...

Supposing your Query1 looks like this:

And your Query2 is this list which I created in advanced editor as a list with this code:

let
    Source = {"column1", "column5", "column6"}
in
    Source

Then, in Query1, you can just use the Home > Remove Columns(dropdown arrrow) > Remove Other Columns to get you started, and then replace the part with the curly braces (including the curly braces themselves) with List.Combine({{"ID"},Query2}). For instance, if you had the Query1 ID column selected like above when you chose Remove Other Columns, you'd see = Table.SelectColumns(Source,{"ID"}). In that case, you'd replace {"ID"} with List.Combine({{"ID"},Query2}).

The List.Combine is to add in ID column, since it is not a part of your Query2 list.

The result would be like this:

Here's what the Query1 code would look like (it pulls a table from excel):

let
    Source = Excel.CurrentWorkbook(){[Name="Query1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source, List.Combine({{"ID"},Query2}))
in
    #"Removed Other Columns"

P.S. If you're bringing in your list of column labels as a string in a single row column from a table, like this as your Query2:

You could change it to a column list by Transform > Split Column > By Delimiter > (choose Comma, Each occurrence of the delimiter, Advance options, Split into Rows and click OK). Then you'll want to do Transform > Format > Trim, to remove stray leading spaces.

In this case, you'd also have to change your Remove Other Columns code, discussed above, from List.Combine({{"ID"},Query2}) to List.Combine({{"ID"},Query2[Column Names]}), to account for the fact you're using the list from the column named Column Names in the table named Query2, rather than just the straight list named Query2 as originally done above.

Power BI / query, select columns dynamically

1 Answers