Conditional grouping

[kulkarni21vinee]

Actual:

Country	City
USA	(M) New York
USA	Greenville
USA	Fredericksburg
USA	(M) Chicago
USA	Burlington
France	(M) Paris
Japan	(M) Tokyo
Japan	Nara
Japan	(M) Osaka
Japan	Nikko
Russia	Kolomna
Russia	(M) Moscow

Expected:

Country	City
USA	Greenville, Fredericksburg, Burlington
USA	(M) New York
USA	(M) Chicago
France	(M) Paris
Japan	Nara, Nikko
Japan	(M) Tokyo
Japan	(M) Osaka
Russia	Kolomna
Russia	(M) Moscow

Rules: PowerBI query to 

1. Group by Country with comma seperated cities

2. Ungroup when city is prefixed with (M) - Major

Is their something like below to ungroup in M query?

#"Grouped Rows" = Table.Group(
#"Expanded Column1",
{"Country"},
{not Text.Contains([City], "(M)")})

[V-yubandi-msft]

Hi @kulkarni21vinee ,

Thanks for connecting with us on the Microsoft Fabric Community Forum. 

I believe the @dufoq3  response is accurate and should address your issue.

If you have any more questions or updates, please feel free to ask, and we will look into it.

If the Super User's answer meets your needs, kindly consider marking it as the Accepted solution.

Thank You.

[wdx223_Daniel]

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcxLCsIwEAbgq5SsFHoJFSooreJjISWLMQ51SMzIxFi8vbZWSHH7/Y+6Vsf9TOVqUk6zCtvsxGKVzn+8FET/JOcwwULwgkLGhnOUJgm6k8WVDDSc6DyKI9882PdYCHiDQ3sLQqHnFdzBD3pg++KRViDwV9sEsGOtyNrvcBdDIPjQmh3fPIyxW5ccDLdK6zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t]),
    Grouped = Table.Group(Source, "Country", {"outer", each Table.Group(Table.Sort(_, "City"), "City", {"inner", each Text.Combine([City], ", ")}, 0, (x,y) => Byte.From(Text.StartsWith(x, "(M)")))}),
    #"Expanded outer" = Table.ExpandTableColumn(Grouped, "outer", {"inner"}, {"inner"})
in
    #"Expanded outer"

[dufoq3 · Accepted solution]

Hi @kulkarni21vinee, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcxLCsIwEAbgq5SsFHoJFSooreJjISWLMQ51SMzIxFi8vbZWSHH7/Y+6Vsf9TOVqUk6zCtvsxGKVzn+8FET/JOcwwULwgkLGhnOUJgm6k8WVDDSc6DyKI9882PdYCHiDQ3sLQqHnFdzBD3pg++KRViDwV9sEsGOtyNrvcBdDIPjQmh3fPIyxW5ccDLdK6zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, City = _t]),
    GroupedRows = Table.Group(Source, {"Country"}, {{"T", each 
        [ a = Table.SelectRows(_, (x)=> not Text.StartsWith(x[City], "(M)"))[City], //except Major
          b = Table.SelectRows(_, (x)=> Text.StartsWith(x[City], "(M)"))[City], //Major
          c = Table.SelectRows(Table.FromColumns( {[Country]} & { if List.IsEmpty(a)then b else {Text.Combine(a, ", ")} & b }, type table[Country=text, City=text]), (x)=> x[City] <> null)
        ][c], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT