Forum Discussion
Solved
Conditional grouping
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)")})
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
4 Replies
Replies have been turned off for this discussion
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 CombinedTlet 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"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.
