Need comma seperated values after I group by

[klehar]

Hi,

this is my Input

This is my expected output (but I want that if the BU compute and storage are present they shold always be present in ascending order). If I sort the BU column before group by that doesnt help.

What can i do?

By the way, this group by is not available through UI. You have to write M code. Here is my code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZU0lFKzs8tKC1JVYrVgQoUl+QXJaZDBYywCcC1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"sales order" = _t, BU = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"sales order", type text}, {"BU", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"BU", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"sales order"},
{

{"BU", each Text.Combine(List.Distinct([#"BU"],Comparer.OrdinalIgnoreCase), ", "), type text}

})
in
#"Grouped Rows"

[klehar]

Can you please explain the moedified table step

[eliasayyy]

@klehar of course , in the modififed step , i just added an index for each BU , so it can know how to properly concatenate them after the last step in the right order

[Anonymous]

Hi @klehar   - here is an alternative solution not using M-code.

In order to obtain
- aggregated comma separated values for column BU
- for each value of the grouping column Sales order
try this solution:

1) In Data view, Create a New Table.
Perform the desired aggregation by defining a table as:

Summarized table = 
// Group text column BU from table 'myTable' by column sales order
// The name column is named aggregated BU. 
// Values separeted by ", "
SUMMARIZE (
    'myTable',
    'myTable'[sales order],
    "Aggregated BU", CONCATENATEX ( 'myTable', 'myTable'[BU], ", " )
)

The snippets below show how the input:

and the result/output:

 
Did this solve your problem?

If so, consider accepting the answer as a solution

Kind regards.

[eliasayyy · Accepted solution]

please try this 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZU0lFKzs8tKC1JVYrVgQoUl+QXJaZDBYywCcC1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"sales order" = _t, BU = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sales order", type text}, {"BU", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"BU", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"sales order"}, {{"Table", each _, type table [sales order=nullable text, BU=nullable text]}}),

    // Transforming the "Table" column to add an index and concatenate values
    #"Modified Tables" = Table.TransformColumns(#"Grouped Rows", {"Table", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    #"Concatenated Values" = Table.TransformColumns(#"Modified Tables", {"Table", each Text.Combine(Table.Column(_, "BU"), ", ")}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Concatenated Values",{{"Table", type text}})

in
    #"Changed Type1"