Possibility to add new reference column for aggregation in Power Query Editor

[Olwin]

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.

[v-yalanwu-msft · Accepted solution]

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.

[PaulDBrown]

What is the criteria/logic to create the final table?

[Olwin]

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

[Olwin]

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: