Group a table, Concatennate ans sum

[GJDealogic]

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

[edhans · Accepted solution]

use measures like this:

Companies = 
CONCATENATEX(
    'Grouping',
    'Grouping'[Company],", "
)


Products = 
CONCATENATEX(
    'Grouping',
    'Grouping'[Product], ", "
)

Returns this:

See this PBIX file for full details.

[edhans · Accepted solution]

use measures like this:

Companies = 
CONCATENATEX(
    'Grouping',
    'Grouping'[Company],", "
)


Products = 
CONCATENATEX(
    'Grouping',
    'Grouping'[Product], ", "
)

Returns this:

See this PBIX file for full details.

[GJDealogic]

This 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

[AlB · Accepted solution]

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