Forum Discussion
Sorting Summarized table
lbendlin i'll try to provide sample data
table 1
| Street | Begin | End |
| Street A | 01-09-23 | 08-09-23 |
| Street A | 02-09-23 | 06-09-23 |
| Street B | 05-09-23 | 12-09-23 |
| Street B | 12-09-23 | 28-09-23 |
| Street C | 14-09-23 | 16-09-23 |
| Street C | 03-09-23 | 04-09-23 |
| Street D | 04-09-23 | 05-09-23 |
| Street D | 11-09-23 | 18-09-23 |
| Street E |
Table 2 (using Summarize based on Table 1 and Street and MINX and MAXX for begin/end date)
| Street | Begin | End | SortOrder |
| Street A | 01-09-23 | 08-09-23 | 1 |
| Street B | 05-09-23 | 28-09-23 | 4 |
| Street C | 03-09-23 | 05-09-23 | 2 |
| Street D | 04-09-23 | 18-09-23 | 3 |
| Street E | 5 |
There is a relationship between the column Street in Table 1 and Street in Table 2 (many to one)
Then I wanted to sort the column Street (in Table 2) based on begin (chronologically) but that didn't work
Everything works and the SortOrder works fine but when I try to use the Sort By Column option in PowerBi desktop it returned the Circuler dependency error
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
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