Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
use measures like this:
Companies =
CONCATENATEX(
'Grouping',
'Grouping'[Company],", "
)
Products =
CONCATENATEX(
'Grouping',
'Grouping'[Product], ", "
)
Returns this:
See this PBIX file for full details.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @GJDealogic
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
use measures like this:
Companies =
CONCATENATEX(
'Grouping',
'Grouping'[Company],", "
)
Products =
CONCATENATEX(
'Grouping',
'Grouping'[Product], ", "
)
Returns this:
See this PBIX file for full details.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis looks great.
Thank you.
Alas I cannot open the pbix as it from a version greater than mine. But I'll try with the formulas anyway (When I learn where to put them)
Gary
Hi @GJDealogic
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.