expand all columns dynamically PROBLEM WITH repeated column name

[ivan00]

Hi,
I can not expand the columns dynamically because these columns could have a name that already exists in the starting table.
I try to explain with an image:


If table2's columns name are "new" I have no problems otherwise if the column name already exists in table1 (in the second case is column named "B") I have the problem in managing the duplicates.
I tried to do some tests combine the list but I can not do it.

You can help me?
I am also attaching the file.

Thank you very much
Ivan

FILE: View attachment ExpandColumns.xlsx
Initial idea:https://community.powerbi.com/t5/De...-with-Unknown-Column-Names/m-p/471964#M219351

 

[Bill Szysz]

Table1e2 query should be

let
    Origine = Table.NestedJoin(Table1,{"A"},Table2,{"D"},"Data",JoinKind.FullOuter),
    Tab_1_ColName = List.Buffer(Table.ColumnNames(Table1)),
    Personalizzato1 = Table.ExpandTableColumn(Origine, "Data", List.Transform(Table.ColumnNames(Table2), each if List.Contains(Tab_1_ColName, _) then _ & ".1" else _ ))in
    Personalizzato1

 

[ivan00]

Hi Bills,
thank you for answer but it would seem that it does not work because values in column b.1 are not shown (see image below):

 

[Bill Szysz]

Sorry, my fault :-((
In the meantime my approach to the problem has changed.
Table2 query

let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella12"]}[Content],
    Tab_1_ColNames = List.Buffer(Table.ColumnNames(Table1)),
    Tab_2_ColNames = Table.ColumnNames(Origine),
    Tab2Headers = List.Transform(Tab_2_ColNames, each if List.Contains(Tab_1_ColNames, _) then _ & ".1" else _ ),
    NewTable2 = Table.RenameColumns(Origine, List.Zip({Tab_2_ColNames, Tab2Headers}))
in
    NewTable2

and then Table1e2 query

let
    Origine = Table.NestedJoin(Table1,{"A"},Table2,{"D"},"Data",JoinKind.FullOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Origine, "Data", Table.ColumnNames(Table2))
in
    #"Expanded {0}"