Forum Discussion

tomperro's avatar
tomperro
Helper V
1 year ago
Solved

Merge Multiple Tables with Different Column Names

I am trying to merge multiple tables with different column names - Can I rename the columns in each table? - Can I create a mapping table and use this in BI somehow? - Are there any tutorials on...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    Table1:

     

    Table2:

     

    Mapping:

     

    Then do the append and column rename:

    // Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t])
in
    Source

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUXIFYjel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t, Column3 = _t, Column4 = _t])
in
    Source

// Append1
let
    Source = Table.Combine({Table1, Table2}),
    #"Renamed Columns" = Table.RenameColumns(Source,List.Zip({Mapping[From],Mapping[To]}),MissingField.Ignore)
in
    #"Renamed Columns"

// Mapping
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSgbIclWJ1YKJGcFEnJFFjuKgzkqgJXNQFSdQULuqqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t])
in
    Source

     

     

lbendlin's avatar
lbendlin
Super User
1 year ago

Please note that "merge"  and "append"  do different things in Power Query. Please clarify which of the actions you need.

 

- yes, you can rename columns

- yes, you can create a mapping table and bulk replace column names

tomperro's avatar
tomperro
Helper V
1 year ago

I need to append the tables. 
How would I create a mapping table and bulk replace column names?

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Table1:

     

    Table2:

     

    Mapping:

     

    Then do the append and column rename:

    // Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t])
in
    Source

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUXIFYjel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t, Column3 = _t, Column4 = _t])
in
    Source

// Append1
let
    Source = Table.Combine({Table1, Table2}),
    #"Renamed Columns" = Table.RenameColumns(Source,List.Zip({Mapping[From],Mapping[To]}),MissingField.Ignore)
in
    #"Renamed Columns"

// Mapping
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSgbIclWJ1YKJGcFEnJFFjuKgzkqgJXNQFSdQULuqqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t])
in
    Source