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
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!
Solved! Go to 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.
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.
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.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |