Removing a dynamic list of columns in powerquery

Question

I'm working on a tool to help my team identify changes in some data files. Long story short, i managed to put something together (I'm quite the beginner with powerquery and M) that works well but it lacks user friendliness.

Issue is that not all team members need the tool to check for differences in all columns (different people, different interests). In order to manage this i used the following to remove all the unneeded columns before doing the compare:

= Table.RemoveColumns(myTable,{"col1","col2","col3"... etc

This works but if you want to change the configuration you need to go into the code and modify the list.

My question is the following: Is there any way to integrate a dynamic list into this code? i.e. have that list of columns in an easy to use table, "tick/untick" the ones you want and have the code remove the rest?

Answer 1

If your intent is to allow the user to select columns without entering the query editor then you may benefit from using a parameter table as described here: http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/ . You should be able to expose a 2colxNrow table to the user with some predefined column names/numbers. You can use data validation to constrain user inputs to a binary on/off behavior ( https://support.office.com/en-us/article/Apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249 ).

( P.S. Based on the your description of your goals Inquire add-in may alread offer the functionality you are looking for )

Answer 2

Probably the easiest way is to use "Choose Columns" on the Home tab in the Query Editor and then rename the generated step like: #"CHOOSE COLUMNS HERE ----->" = Table.SelectColumns(Source,{"Column1", "Column2", "Column3", "Column5", "Column7", "Column8", "Column9", "Column10"})

Then when you want to adjust the selected columns, you can press the small wheel to which the arrow is pointing, and a popup will show up from which you can do your (un)ticking.

Alternatively, if you use multiple queries with the same selection, you can create an additional query that outputs a list, like:

let
    Source = Table.FromList(List.Transform({1..10}, each "Column" & Text.From(_)),null,{"Available Columns"}),
    Transposed = Table.Transpose(Source),
    #"CHOOSE COLUMNS HERE ----->" = Table.SelectColumns(Transposed,{"Column2", "Column3", "Column5",  "Column6", "Column8", "Column9", "Column10"}),
    TransposedBack = Table.Transpose(#"CHOOSE COLUMNS HERE ----->"),
    ConvertedToList = TransposedBack[Column1]
in
    ConvertedToList

And then use that list in your queries, like: = Table.SelectColumns(#"Transposed Table",SelectedColumns) where SelectedColumns is the name of the query with the selected columns.