Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Olwin
Frequent Visitor

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 NoCategoryAmountTransaction No.
ABLUE20001
BBLUE30002
CYELLOW10003
CYELLOW5004

 

Example of Item Master Table

 

ItemCategory
ABLUE
BYELLOW
CRED

 

I would like to aggregate my Transaction Table to become like this:

CategoryAmount
BLUE2000
RED1500
YELLOW3000

 

Kindly please advice.

Thank you.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Olwin ;

You could merge table2 to table 1.such as:

1. Merge two tables.

vyalanwumsft_0-1633918811347.png

2.Expand Category column .

vyalanwumsft_1-1633918908181.png

3.Group by it.

vyalanwumsft_2-1633919033989.png

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:

vyalanwumsft_3-1633919099425.png

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.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @Olwin ;

You could merge table2 to table 1.such as:

1. Merge two tables.

vyalanwumsft_0-1633918811347.png

2.Expand Category column .

vyalanwumsft_1-1633918908181.png

3.Group by it.

vyalanwumsft_2-1633919033989.png

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:

vyalanwumsft_3-1633919099425.png

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
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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 NoCategoryAmountTransaction No.Category - Item Master
ABLUE20001BLUE
BBLUE30002YELLOW
CYELLOW10003RED
CYELLOW5004RED

 

By this, next I can group my data based on "Category - Item Master"

Category - Item MasterAmount
BLUE2000
RED1500
YELLOW3000

 

Thanks.

PaulDBrown
Community Champion
Community Champion

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

2021-10-11.jpgThe you can use the Category field from the Item Master table and the SUM of the Amount to get:

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.