Forum Discussion

PowerBI_Query's avatar
PowerBI_Query
Icon for Helper II rankHelper II
4 years ago
Solved

Group by Sets that are Not in the Same Order

I want to group similar sets from a list of values in column B. Initially I will wrap the array with UNIQUE function to remove all duplicates. Then use that unique array to group by repeating sets i...
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi PowerBI_Query 

     

    Do you want to keep the original order of ColumnA? and use Power Query?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lHyy1dwcVeK1YGIGAFF3EpzclAEMJUEJBaVZCYiVJmhazPDoQjVKGN0bcZYlfhmFhdn5qXDxSzQtVngUIRqlAm6NhOwkhKFxIKCnMzkxKScVBQpdPebo+s3x6EIh5mmuKUscUsZGmB4xBAp3mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Heading1 = _t, Heading2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading1", type text}, {"Heading2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{ {"Heading2", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Heading1"}, {{"allrows",each Text.Combine(List.Distinct([Heading2]),",")}})
in
    #"Grouped Rows"

     

PowerBI_Query's avatar
PowerBI_Query
Icon for Helper II rankHelper II
4 years ago

Thank you it worked.

I removed List.Distinct step it is working fine

I removed Index step it is working fine

It is in original order after group by why index column was added?

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading1", type text}, {"Heading2", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{ {"Heading2", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Heading1"}, {{"allrows",each Text.Combine([Heading2],",")}})
in
#"Grouped Rows"

Can I get the count of each set agaist the Names

E.g

No DG 3

Not applicable 2

Full,No DG,Missing 2 

etc...

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi PowerBI_Query 

 

Good catch, the Index was in wrong step, it was to keep the allrows in the same order, you can see Name 6 & Name 8 were not in the same order...and this time, Name is not the original order...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lHyy1dwcVeK1YGIGAFF3EpzclAEMJUEJBaVZCYiVJmhazPDoQjVKGN0bcZYlfhmFhdn5qXDxSzQtVngUIRqlAm6NhOwkhKFxIKCnMzkxKScVBQpdPebo+s3x6EIh5mmuKUscUsZGmB4xBAp3mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Heading1 = _t, Heading2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading1", type text}, {"Heading2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{ {"Heading2", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Heading1"}, {{"allrows",each Text.Combine([Heading2],",")}})
in
    #"Grouped Rows"

you can group by allrows again, the count the rows

 

or use DAX, it is a table visual