Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter table based on date

Hello, I have the following table loaded in Power BI (Power Query). The table is called the Reference table.   Student Name To submit as from Submission optional (Yes/No) Lizui 02 July 20...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9BC4JAEIX/iuzZhTI6dDShICoiu4R4GHG0pXV3WZuD/fomTUSCOey8N9/smywTR/UmJUIRyQNpGUX8PFuRh+wAVWgK8jVrXHdse/2GNWgHDp5fbCNjqmdc7IMrFKqDx8D14ha1Zs6XOP+rN/dg2ILGkR+QMQGZF7Tcr+UJugEYU+w8quACpC2Ly8WUYhxIEUvrK1ZWf6cltoUGtOUjTFBikIB36rcoRTfN5h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, #"To submit as from" = _t, #"Submission optional (Yes/No)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"To submit as from", type date}, {"Submission optional (Yes/No)", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [To submit as from]<=Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),1) or [To submit as from]=null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"To submit as from", "Submission optional (Yes/No)", "Custom"})
in
    #"Removed Columns"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9BC4JAEIX/iuzZhTI6dDShICoiu4R4GHG0pXV3WZuD/fomTUSCOey8N9/smywTR/UmJUIRyQNpGUX8PFuRh+wAVWgK8jVrXHdse/2GNWgHDp5fbCNjqmdc7IMrFKqDx8D14ha1Zs6XOP+rN/dg2ILGkR+QMQGZF7Tcr+UJugEYU+w8quACpC2Ly8WUYhxIEUvrK1ZWf6cltoUGtOUjTFBikIB36rcoRTfN5h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student Name" = _t, #"To submit as from" = _t, #"Submission optional (Yes/No)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student Name", type text}, {"To submit as from", type date}, {"Submission optional (Yes/No)", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [To submit as from]<=Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),1) or [To submit as from]=null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"To submit as from", "Submission optional (Yes/No)", "Custom"})
in
    #"Removed Columns"

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Anonymous Adding the custom column is a great way to visualize which rows get filtered but it's also possible to do the filtering in one step with exactly the same logic rather than the three steps of adding a column, filtering that column, and then deleting that column.

 

The /*[abbreviated]*/ query would look like this instead:

let
    Source = Table.FromRows(/*[...]*/),
    #"Changed Type" = Table.TransformColumnTypes(Source, {/*[...]*/}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type",
        each ([To submit as from] <= Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),1) or
              [To submit as from] = null))
in
    #"Filtered Rows"