Forum Discussion
Solved
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 Am...
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.
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.
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.