Power BI Query: Need to Select Columns in Table 1 based on list of column names in Table 2

Question

I have merged data from two tables (Table#1 and Table #2) into single master data table (Table #3) using power query. Table #4 and Table #5 contain list of column names from Table #1 and Table #2, respectively, that I want to include in Table #3. I have inserted both these tables into power query as "connection only tables".

Instead of doing this manually each time I want my power query to be updated dynamically each time Table #4 and/or Table #5 are updated.

I am trying to do this Table #4 and Table #5 queries within the Table.SelectColumns function in the advanced query editor to do this. If so, please help and share the solution code.

Thanks in advance.

Answer 1

Not 100% sure I understand what you need but I have given it a try.

Let's say your Table_4 looks like this:

Column_name
Row_Update
Row_ID

Click on Convert to List inside Power Query, Table_4 will now be a list [Row_update, Row_ID].

Table_1 looks like this:

Name  Value  Row_Update  Row_ID
'a'   10     2019        1
..    ..     ..          ..

Inside the query for Table_1 you can now use this list inside the SelectColumns, something like this:

let
    Source = Sql.Databases("localhost"),
    my_db = Source{[Name="my_db"]}[Data],
    Table_1 = my_db{[Schema="my_schema",Item="Table_1"]}[Data],
    // have manually modified the code Remove Other Columns created
    // replacing the selected columns with a reference to the list
    #"Remove Other Columns" = Table.SelectColumns(Table_1,#"Table_4")
in
    #"Remove Other Columns"

And now Table_1 will look like this:

Row_Update  Row_ID
2019        1
..          ..