Forum Discussion
Group a table, Concatennate ans sum
Hi
Give a granula table like this
Region Product Company Value
EMEA Salt SaltyCo 100
EMEA Vinegar VinCo 350
APAC Red Sauce RedCo 400
APAC Brown Sauce Brco 450
How can I group to this
Region Product Company Value
EMEA Salt, Vinegar SaltyCo, VinCo 450
APAC Red Sauce, Brown Sauce RedCo,Brco 850
Simple in SQL, tough in PBI (for me)
PLease help if you can
Rgds
Gary
use measures like this:
Companies = CONCATENATEX( 'Grouping', 'Grouping'[Company],", " ) Products = CONCATENATEX( 'Grouping', 'Grouping'[Product], ", " )Returns this:
Hi Anonymous
If you want to do it in Power Query, paste this in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvV1dVTSUQpOzCmBUpXO+UCWoYGBUqwOXD4sMy81PbEIwgIrMDaFKHAMcHQGcoNSUxSCE0uTUyFssBITAxQlTkX55XlwRU5FyWA1IGNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Product = _t, Company = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Product", type text}, {"Company", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Region"}, {{"Product", each Text.Combine([Product], ", "), type text},{"Company", each Text.Combine([Company], ", "), type text}, {"Value", each List.Sum([Value]), type number}}) in #"Grouped Rows"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
3 Replies
use measures like this:
Companies = CONCATENATEX( 'Grouping', 'Grouping'[Company],", " ) Products = CONCATENATEX( 'Grouping', 'Grouping'[Product], ", " )Returns this:
Hi Anonymous
If you want to do it in Power Query, paste this in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvV1dVTSUQpOzCmBUpXO+UCWoYGBUqwOXD4sMy81PbEIwgIrMDaFKHAMcHQGcoNSUxSCE0uTUyFssBITAxQlTkX55XlwRU5FyWA1IGNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Product = _t, Company = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Product", type text}, {"Company", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Region"}, {{"Product", each Text.Combine([Product], ", "), type text},{"Company", each Text.Combine([Company], ", "), type text}, {"Value", each List.Sum([Value]), type number}}) in #"Grouped Rows"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers