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) O...
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
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"