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 how to merge multiple tables with different column names?
- Other Options???

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

     

     

5 Replies

  • 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

         

         

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    Hi tomperro 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

     

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    Hi tomperro ,

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


    Thank you.