none
Power Query M Language Filter RRS feed

  • Question

  • I am trying to append selected rows from several tables into one table.

    My first step is to select the rows I want (all rows with a date before or on the current date).

    So I have this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Public_Holidays"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Holiday", type text}, {"Fixed/Variable date", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Fixed/Variable date"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date] <= #datetime(2018, 4, 17, 0, 0, 0))
    in
        #"Filtered Rows"

    But when I change it to this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Public_Holidays"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Holiday", type text}, {"Fixed/Variable date", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Fixed/Variable date"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date] <= DateTime.FixedLocalNow)
    in
        #"Filtered Rows"

    I get this error:

    Expression.Error: We cannot apply operator < to types Function and DateTime.
    Details:
        Operator=&lt;
        Left=Function
        Right=1/01/2018 12:00:00 AM

    For the life of me, I cannot figure out how to get this to evaluate correctly. I have scoured https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-function-reference but I cannot find a solution.

    Any help would be appreciated.

    thanks


    Jacques Raubenheimer http://insight.trueinsight.za.com

    Tuesday, April 17, 2018 12:07 AM

Answers

  • See if below helps

      #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date] <= DateTime.FixedLocalNow())

    Empty () added.


    Best Regards, Asadulla Javed


    Tuesday, April 17, 2018 6:25 AM
    Answerer

All replies

  • See if below helps

      #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date] <= DateTime.FixedLocalNow())

    Empty () added.


    Best Regards, Asadulla Javed


    Tuesday, April 17, 2018 6:25 AM
    Answerer
  • Arrgghhh! How did I miss that!

    Thank you, Asadulla.


    Jacques Raubenheimer http://insight.trueinsight.za.com

    Tuesday, April 17, 2018 11:51 PM