Forum Discussion
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.
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.
5 Replies
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.
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:
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.
