Forum Discussion

Redzero1's avatar
Redzero1
Frequent Visitor
2 years ago
Solved

Union function in Power Query

Hello,

I have a simple request for M Query experts :

 

I have 3 different Tables ( Table1 , Table2, Table3) with different structure for each of them 

Table1 (Sales , Amount , Date , region , phone) 

Table2 (sales , product, Trade_date, amount , color)

Table3( VAT , amount , sales , client , date , supervisor)

 

what I want to have is One New table with the commun fields Table (Sales, Date , Sales) aggregating all information .

 

I managed to use UNION function in DAX but I want to see if can do it in Power Query using Table.Combine but with selected columns something like the below :

= Table.Combine({#"Table1", #"Table2", #"Table3"}) 

 

thanks for your help 

Need Help
  • mtayyab07's avatar
    mtayyab07
    2 years ago

    Please try this code

    let
    // Correct the column names and select common columns for Table1
    Table1_Modified = Table.SelectColumns(Table.RenameColumns(Table1, {{"Amount", "Amount"}, {"Date", "Date"}, {"Sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Correct the column names and select common columns for Table2
    Table2_Modified = Table.SelectColumns(Table.RenameColumns(Table2, {{"amount", "Amount"}, {"Trade_date", "Date"}, {"sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Correct the column names and select common columns for Table3
    Table3_Modified = Table.SelectColumns(Table.RenameColumns(Table3, {{"amount", "Amount"}, {"date", "Date"}, {"sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Combine the tables
    CombinedTable = Table.Combine({Table1_Modified, Table2_Modified, Table3_Modified})
in
    CombinedTable

4 Replies

Replies have been turned off for this discussion
  • mtayyab07's avatar
    mtayyab07
    Frequent Visitor
    2 years ago

    Please try this code

    let
    // Correct the column names and select common columns for Table1
    Table1_Modified = Table.SelectColumns(Table.RenameColumns(Table1, {{"Amount", "Amount"}, {"Date", "Date"}, {"Sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Correct the column names and select common columns for Table2
    Table2_Modified = Table.SelectColumns(Table.RenameColumns(Table2, {{"amount", "Amount"}, {"Trade_date", "Date"}, {"sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Correct the column names and select common columns for Table3
    Table3_Modified = Table.SelectColumns(Table.RenameColumns(Table3, {{"amount", "Amount"}, {"date", "Date"}, {"sales", "Sales"}}), {"Sales", "Date", "Amount"}),

    // Combine the tables
    CombinedTable = Table.Combine({Table1_Modified, Table2_Modified, Table3_Modified})
in
    CombinedTable
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Redzero1 

    You can create a blank query and put the following code to advanced editor 

    let
    SelectColumnfromTable1=Table.SelectColumns(Table1,{"Sales","Date","Amount"}),
    SelectColumnfromTable2=Table.SelectColumns(Table2,{"sales","Trade_date","amount"}),
    Renametable2=Table.RenameColumns(SelectColumnfromTable2,{{"sales", "Sales"},{"Trade_date","Date"},{"amount","Amount"}}),
    SelectColumnfromTable3=Table.SelectColumns(Table3,{"sales","date","amount"}),
    Renametable3=Table.RenameColumns(SelectColumnfromTable3,{{"sales", "Sales"},{"date","Date"},{"amount","Amount"}}),
    combinetables = Table.Combine({Renametable3, Renametable2,SelectColumnfromTable1})
in
     combinetables

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.