Forum Discussion
Sorting Summarized table
What should happen if multiple streets have the same Begin date?
lbendlin then it should look at the date in End and if both are the same then just sort alphabetically
- lbendlin2 years agoSuper User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSk0tUXBU0lEyMNQ1sNQ1MgYxLaDMWB1UJUYIJWaYSpxA4qZwJYZG2JUYIkwxwmKRM0iJCcIULBaBlBgYI9xigqnEBVkcyVloSgwRnjbE4hZXoLgCGMfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Street = _t, Begin = _t, End = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Street", type text}, {"Begin", type date}, {"End", type date}},"nl"), #"Grouped Rows" = Table.Group(#"Changed Type", {"Street"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"End", each List.Max([End]), type nullable text}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"End", Order.Ascending}, {"Street", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type) in #"Added Index"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Note: you haven't indicated how to sort nulls. Solved: Sort text by ascending and make nulls last - Microsoft Fabric Community
- RonaldvdH2 years agoPost Patron
lbendlin I'm sure I did something wrong 🙂
Ive chosen a new query, pasted your code in the advanced editor and saved the whole thing.
But...nothing happens.Somehow/somewhere I need to use that query in the visual or something right ?
- lbendlin2 years agoSuper User
yes the query will appear as a new table in Power BI, and you can add its columns to a visual as needed.