Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Sorting Summarized table

Hey Guys,   I've got a summarized table based on unique streetnames and then a couple of date columns (begin and end) Those date columns are based on another table using MINX and MAXX I want to s...
Need Help
Tips and Tricks
RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

lbendlin I wish it were that simple to me but i'm still not getting which part I need to change so can you hold my hand 

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,{{"Adres straat", type text}, {"Begin", type date}, {"Eind", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adres straat"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"Eind", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"Eind", Order.Ascending}, {"Adres straat", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"

Which part do I need to remove and where do I reference the summarized table Streets

In het Advancend editor I can only see the original tabel and this query and NOT the summarized table obviously but If you could explain it to my if I was a 5 year old it would help  

lbendlin's avatar
lbendlin
Super User
2 years ago

Something like this

let
    Source = #"New Summarized Table",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Adres straat", type text}, {"Begin", type date}, {"Eind", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adres straat"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"Eind", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"Eind", Order.Ascending}, {"Adres straat", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"