Forum Discussion

River's avatar
River
Helper IV
5 years ago
Solved

create a union table with new column names

Hi Friend,

 

I need to create a union table from two different tables, with different column name. Let's say union SalesOrder and PurchOrder number into new table's new column called SOPO.

 

How can I do it?

 

Many thanks!

  • You need to rename the column in the 2nd table River . You can do it before Append operation, or during the append operation. For example, here is Table1 and Table2

    THis code does a Union into the new SPMO column

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesOrder = _t]),
        #"Appended Query" = 
            Table.Combine(
                {
                    Table.RenameColumns(Source, {{"SalesOrder", "SPMO"}}),
                    Table.RenameColumns(Table2, {{"PurchaseOrder","SPMO"}})
                }
            )
    in
        #"Appended Query"

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

    Here is my PBIX file to help you see it work.

     

     

     

5 Replies

  • From the home tab choose Merge queries as new select the two tables and the columns that join them.

    • River's avatar
      River
      Helper IV

      Hi JW,

       

      How do you merge them into same column? It appears to me It's more of join.

       

      Thanks,

      River

  • edhans's avatar
    edhans
    Community Champion

    You need to rename the column in the 2nd table River . You can do it before Append operation, or during the append operation. For example, here is Table1 and Table2

    THis code does a Union into the new SPMO column

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesOrder = _t]),
        #"Appended Query" = 
            Table.Combine(
                {
                    Table.RenameColumns(Source, {{"SalesOrder", "SPMO"}}),
                    Table.RenameColumns(Table2, {{"PurchaseOrder","SPMO"}})
                }
            )
    in
        #"Appended Query"

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.

     

    Here is my PBIX file to help you see it work.