Search code examples
excelpowerbipowerquerydata-analysispowerbi-desktop

Filter for last 120 minutes in power query

I want to filter a Date/Time column for the latest 120 minutes.

This is not working:

= Table.SelectRows(#"Added Custom", each [#"Datum/Tid"] >=Duration.Minutes(DateTime.LocalNow() -(120))

enter image description here

let
    Source = Table.Combine({tab_Silosar, tab_Mjölk, tab_Grädde, tab_Restmjölk, tab_Gränsmjölk, tab_SötGränsmjölk, tab_Permeat, tab_Pastörer, tab_Filråvara, tab_Hydrolys, tab_Syrning}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Tank] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"ID", "Streckkod", "Datum", "Tid", "Tank", "Produkt", "Fett", "Protein", "TS", "Laktos", "SNF", "Glukos", "pH", "Nitrat", "Nitrit", "Temp", "Ålder", "Volym", "LoS", "UTS", "Konsistens", "Antibiotika", "Sign", "Kommentar"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", type any}, {"Streckkod", type text}, {"Datum", type date}, {"Tid", type time}, {"Tank", type text}, {"Produkt", type text}, {"Fett", type number}, {"Protein", type number}, {"TS", type number}, {"Laktos", type number}, {"SNF", type number}, {"Glukos", type number}, {"pH", type number}, {"Nitrat", type number}, {"Nitrit", type number}, {"Temp", type number}, {"Ålder", type number}, {"Volym", Int64.Type}, {"LoS", Int64.Type}, {"UTS", Int64.Type}, {"Konsistens", Int64.Type}, {"Antibiotika", type text}, {"Sign", type text}, {"Kommentar", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Datum/Tid", each [Datum]&[Tid]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Datum/Tid", type datetime}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([#"Datum/Tid"] >= ( DateTime.LocalNow() - #duration(0,1,20,0)) ))
in
    #"Filtered Rows1"
Solution

  • Try this.

    = Table.SelectRows(#"Changed Type", each ([#"Datum/Tid"] >= ( DateTime.LocalNow() - #duration(0,1,20,0)) ))

    This should be the code (you were referring to the wrong previous step):

    let
    Source = Table.Combine({tab_Silosar, tab_Mjölk, tab_Grädde, tab_Restmjölk, tab_Gränsmjölk, tab_SötGränsmjölk, tab_Permeat, tab_Pastörer, tab_Filråvara, tab_Hydrolys, tab_Syrning}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Tank] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"ID", "Streckkod", "Datum", "Tid", "Tank", "Produkt", "Fett", "Protein", "TS", "Laktos", "SNF", "Glukos", "pH", "Nitrat", "Nitrit", "Temp", "Ålder", "Volym", "LoS", "UTS", "Konsistens", "Antibiotika", "Sign", "Kommentar"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", type any}, {"Streckkod", type text}, {"Datum", type date}, {"Tid", type time}, {"Tank", type text}, {"Produkt", type text}, {"Fett", type number}, {"Protein", type number}, {"TS", type number}, {"Laktos", type number}, {"SNF", type number}, {"Glukos", type number}, {"pH", type number}, {"Nitrat", type number}, {"Nitrit", type number}, {"Temp", type number}, {"Ålder", type number}, {"Volym", Int64.Type}, {"LoS", Int64.Type}, {"UTS", Int64.Type}, {"Konsistens", Int64.Type}, {"Antibiotika", type text}, {"Sign", type text}, {"Kommentar", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Datum/Tid", each [Datum]&[Tid]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Datum/Tid", type datetime}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([#"Datum/Tid"] >= ( DateTime.LocalNow() - #duration(0,1,20,0)) ))
in
    #"Filtered Rows1"