Forum Discussion
Possibility to add new reference column for aggregation in Power Query Editor
Hi there ,
I have a scenario where my detail item transaction didn't have exact "Category" compared to "Category" in item master data.
Example of Item Detail Transaction Table:
| Item No | Category | Amount | Transaction No. |
| A | BLUE | 2000 | 1 |
| B | BLUE | 3000 | 2 |
| C | YELLOW | 1000 | 3 |
| C | YELLOW | 500 | 4 |
Example of Item Master Table
| Item | Category |
| A | BLUE |
| B | YELLOW |
| C | RED |
I would like to aggregate my Transaction Table to become like this:
| Category | Amount |
| BLUE | 2000 |
| RED | 1500 |
| YELLOW | 3000 |
Kindly please advice.
Thank you.
Hi， Olwin ；
You could merge table2 to table 1.such as:
1. Merge two tables.
2.Expand Category column .
3.Group by it.
Here is M language.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLyCXUFUkYGBgZAylApVidayQkhbgwRNwKLOwNZka4+Pv7hIKUQGWNMGVOwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item No" = _t, Category = _t, Amount = _t, #"Transaction No." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item No", type text}, {"Category", type text}, {"Amount", Int64.Type}, {"Transaction No.", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item No"}, #"Item Master", {"Item"}, "Item Master", JoinKind.LeftOuter), #"Expanded Item Master" = Table.ExpandTableColumn(#"Merged Queries", "Item Master", {"Category"}, {"Item Master.Category"}), #"Grouped Rows" = Table.Group(#"Expanded Item Master", {"Item No", "Item Master.Category"}, {{"Category", each List.Max([Category]), type nullable text}, {"Amount", each List.Sum([Amount]), type nullable number}}), #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Item No", "Item Master.Category", "Amount", "Category"}) in #"Reordered Columns"The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
PaulDBrownCommunity Champion
What is the criteria/logic to create the final table?
Hi PaulDBrown
Sorry I was not mention it clearly on my initial post. My plan is to add new column (Category - Item Master) on the Item Detail Transaction Table, which is refered to Category field on Item Master table. So it will look like below:
Item No Category Amount Transaction No. Category - Item Master A BLUE 2000 1 BLUE B BLUE 3000 2 YELLOW C YELLOW 1000 3 RED C YELLOW 500 4 RED By this, next I can group my data based on "Category - Item Master"
Category - Item Master Amount BLUE 2000 RED 1500 YELLOW 3000 Thanks.
- PaulDBrown
Create a one-to-many relationship between the Item field in the master table and the corresponding field in the transaction table. Then use the Category field from the master table in you visuals.
EDIT:
Added an example.The model
The you can use the Category field from the Item Master table and the SUM of the Amount to get:
Hi PaulDBrown Thanks for your reply, but what I need is to reduce transaction table using grouping, so need to be done in Power Query Editor.
Hi v-yalanwu-msft Thanks for your solution, merging process has addressed my needs 😉
Best Regards,
Olwin
v-yalanwu-msftCommunity Support
Hi， Olwin ；
You could merge table2 to table 1.such as:
1. Merge two tables.
2.Expand Category column .
3.Group by it.
Here is M language.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLyCXUFUkYGBgZAylApVidayQkhbgwRNwKLOwNZka4+Pv7hIKUQGWNMGVOwhIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item No" = _t, Category = _t, Amount = _t, #"Transaction No." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item No", type text}, {"Category", type text}, {"Amount", Int64.Type}, {"Transaction No.", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item No"}, #"Item Master", {"Item"}, "Item Master", JoinKind.LeftOuter), #"Expanded Item Master" = Table.ExpandTableColumn(#"Merged Queries", "Item Master", {"Category"}, {"Item Master.Category"}), #"Grouped Rows" = Table.Group(#"Expanded Item Master", {"Item No", "Item Master.Category"}, {{"Category", each List.Max([Category]), type nullable text}, {"Amount", each List.Sum([Amount]), type nullable number}}), #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Item No", "Item Master.Category", "Amount", "Category"}) in #"Reordered Columns"The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
