Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
alice11987
Helper I
Helper I

Update Table based on another table

Hi there,

 

I have two tables, Sales table & Backorder table.

 

I will need to include Backorder table into Sales table based on Date & Inv ID: 

1. Check if Date & Inv ID is the same; add the Amount from Table B into Table A

2. If Date is not the same & Inv ID is the same; append from Table B into Table A

3. If Date is the same & Inv is note the same; append from Table B into Table A

4. Don't change anything on Table B

 

What would be the best way to do it. e..g Power Query? DAX? and How to do it.

 

alice11987_0-1675907493320.png

 

Thank you!

1 ACCEPTED SOLUTION

Hi,

Does this M code work?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inv ID", Int64.Type}, {"Sales type", type text}, {"Amount", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Table2}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Date", "Inv ID"}, {{"Total", each List.Sum([Amount]), type nullable number}, {"Type", each List.Max([Sales type]), type nullable text}})
in
    #"Grouped Rows"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Append the two tables.  Select Date and Inv ID columns, group by them and sum the numbers in the amount column.

Hope this helps. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thank you for reply. Can I know if it is possible if I would like to keep other columns from Table A when I group by. 

 

For e.g., There is "Period" column in Table A and not in Table B, but end result, I would like to remain "Period" column as follow:

alice11987_0-1675913045250.png

thank you

 

Hi,

Does this M code work?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Inv ID", Int64.Type}, {"Sales type", type text}, {"Amount", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Table2}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"Date", "Inv ID"}, {{"Total", each List.Sum([Amount]), type nullable number}, {"Type", each List.Max([Sales type]), type nullable text}})
in
    #"Grouped Rows"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thank you so much! One last question...

 

What if I append the two tables as new query, how should I edit on the above M code to keep all other columns when I group by?

 

thank you

You are welcome.  Everything should remain the same.  Just try it through the UI yourself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.