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  yes and no because it does appear as a table but it's not linked to the right source. It's linked to your testfile i'm guessing.

The table in my rapport it has to link with is the table Straten, so how do I do that ?

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"

 

I've changed the column names to the exact column names in my rapport but the source has to be altered to my table Straten 

lbendlin's avatar
lbendlin
Super User
2 years ago

Change the Source bit to point to your own source table.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    let

       Source=#"New Summarized Table",

    ...

  • 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"
  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin and how do I do that ? 

    I have 1 original table and 1 summarized new table based ont the orginal table.

     

    That means the query should be linked to the new summarized table

  • 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