Dynamic Merge Query with Ability to Iteratively Rename Duplicate Columns

Question

I have a fun challenge for anyone interested…

Basically, what I'm looking for is a method to iteratively rename duplicate columns within my dynamic merge statement. I want to replicate how clicking "expand" on a nested table column handles renaming duplicate columns with .1, .2, .3, .4, etc... I'm thinking maybe a recursive function, but I can't get that to work.

I have a long list of queries that are dependent upon each other. It starts with 2 independent queries that are simply each importing a table. Then, those two queries are joined together via Merge as New and "expanding" the nested table column which the 2nd query resided in.

The first challenge is that Power BI hard-codes the column names, so I needed to make that merge dynamic, since changes are often made to the 2 queries being joined and need to be reflected in the merge column. 2 things should be noted - 1, the primary and foreign key have different names (which is also for the most part true among all the queries following) and 2, there are duplicate columns in query 1 and query 2. Therefore, in this statement, I included a clause to mark any duplicate columns in query 2 that exist in query 1 with ".1" appended to its name:

 let
Source = Table.NestedJoin(query1, {"PrimaryKey"}, query2, {"ForeignKey"}, "query2", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Source, "query2", Table.ColumnNames(query2), List.Transform(Table.ColumnNames(query2) as list, each if List.Contains(Table.ColumnNames(Source) as list, _) then _ & ".1" else _))
 in Expanded

Now this worked great, until I got further downstream to other merge queries. More independent tables are brought in, merged to the merge, and merges are even merged to merges. The problem I ultimately ran into is that "column.1" already exists, which broke the subsequent queries.

Ultimately, the code would need to check for any duplicate columns amongst the 2 queries being merged. If a duplicate is identified but hasn't yet been marked with .#, .1 should be appended to the column name of the 2nd query column. Similarly, if a duplicate is identified and it ends with .1, it should then be changed to .2. And so on and so forth. I know there is some way to do this, probably with some sort of iterative loop, but it is beyond my expertise.

Is there any way to accomplish this? Bonus points if you can write me working code. Thank you very much in advance!

horseyride · Accepted Answer · 2023-12-15 14:54:40Z

0

Sample Table2 code to merge in Table1, creating expansion column names for Table1 by appending sequential numbers to duplicate names

(This seems a crazy thing to do)

let

   rename = (RenameMe as table, Comparison as table) as list=> let
       Mutate= List.Transform(Table.ColumnNames(RenameMe), each if List.Contains(Table.ColumnNames(Comparison),_) then 
       try Text.Combine(List.RemoveLastN(Text.Split(_,"."),1),".")&"."&Text.From(Number.From(List.Last(Text.Split(_,".")))+1)
       otherwise _&".1"  else _) in Mutate,

Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"dd"}, Table1, {"dd"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", Table.ColumnNames(Table1), rename(Table1,Source))
in  #"Expanded Table1"

alternative with Text.BeforeDelimiter

let

    rename = (RenameMe as table, Comparison as table) as list=> let
        Mutate= List.Transform(Table.ColumnNames(RenameMe), each if List.Contains(Table.ColumnNames(Comparison),_) then 
       try Text.BeforeDelimiter(_, ".", {0, RelativePosition.FromEnd})&"."&Text.From(Number.From(Text.AfterDelimiter(_, ".", {0, RelativePosition.FromEnd}))+1)
       otherwise _&".1"  else _) in Mutate,

Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"dd"}, Table1, {"dd"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", Table.ColumnNames(Table1), rename(Table1,Source))
in  #"Expanded Table1"

edited Dec 15, 2023 at 14:54

answered Dec 15, 2023 at 0:11

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hbowen Over a year ago

Do you think the following code is feasible, if not how would you revise it? Really grasping at straws here but interested in your input. = Table.ExpandTableColumn( Source, "query2", List.Transform( Table.ColumnNames(query2), each _ & ( "." & Text.From( List.Sum( List.Repeat( {1}, List.Count( List.Select( Table.ColumnNames(Source), (x) => _ = Text.BeforeDelimiter(x, ".", {0, RelativePosition.FromEnd}) or _ = x)))) ?? 0)))) ------------------------------------ For reference, Source = Table.NestedJoin(query1, {"PrimaryKey"}, query2, {"ForeignKey"}, "query2", JoinKind.LeftOuter),

horseyride Over a year ago

Answer edited. If that doesnt work, perhaps someone else will help

Collectives™ on Stack Overflow

Dynamic Merge Query with Ability to Iteratively Rename Duplicate Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related