Power Query: Expand Columns based on Condition

Power Query
1

Hi

I know how to expand columns dynamically BUT, how might I do this ONLY for certain columns.

I am merging 2 tables and only want to add certain columns from the second table. The columns I want all start with Doc. I may added new columns starting with “Doc” so that the query will add all columns starting with “Doc” dynamically (because I may add more in the future).

Feel sure it’s doable… but haven’t succeeded. All help appreciated. Would be a ‘nice to have’.

Thanks

Erica

2

Hi @Ericadyson ,

You can try with this:

let
    Source = Table.NestedJoin(Table1, {"Column"}, Table2, {"Column"}, "Table2", JoinKind.LeftOuter),
      DocColumnList = List.Buffer(List.Select(Table.ColumnNames(Table2), each Text.StartsWith(_ , "Doc") )),
    ExpTable = Table.ExpandTableColumn(Source, "Table2", DocColumnList ) 
in
    ExpTable

Sample pbix
Example_for_Erica.pbix (41.1 KB)

Hope it helps.

Best regards,
Maja

2 Likes
3

Hi Maja

Brilliant. Thanks a bundle. That’s excellent. Mega thanks.

1 Like
4

You are welcome. I am glad that I can help and that you found solution.

If you also need reverse syntax (in some situation) and you have situation that you want exclude column that not end with some string you can add like example (and not End with 7):

and not Text.EndsWith(_, “7”)

Good luck with your query.