Power Query - Date error after filtering (but not before)

[Anonymous]

Hello,

I am receiving the following error when trying to filter a date column to only include dates up to and including last month:
"DataFormat.Error: We couldn't parse the input provided as a Date value."

Previous step showing no error ([Date] field already converted to Date format without issue):
Previous step with no error

Date filter producing error:

Error after filtering date

I have 4 near-identical files (duplicated tabs in Excel) and this is the only one that is producing this error.

Here is the M code:

let
    Source = Excel.Workbook(File.Contents("\\gbbgkwvsrv3\Berkley\Reporting\CAPEX Reporting\2019\Capital Investment\Speculative DCs\NETEX\06.Jun20\NETEX.SpecDC.Jun20.xlsx"), null, true),
    #"PBI.Model(NETEX.MRC)_Sheet" = Source{[Item="PBI.Model(NETEX.MRC)",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"PBI.Model(NETEX.MRC)_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type any}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Route", type text}, {"CLLI", type text}, {"OSP.StartDate", type date}, {"End.Date", type date}, {"NRC", type number}, {"MRC", type number}, {"01/08/2018", Int64.Type}, {"01/09/2018", Int64.Type}, {"01/10/2018", type number}, {"01/11/2018", type number}, {"01/12/2018", type number}, {"01/01/2019", type number}, {"01/02/2019", type number}, {"01/03/2019", type number}, {"01/04/2019", type number}, {"01/05/2019", type number}, {"01/06/2019", type number}, {"01/07/2019", type number}, {"01/08/2019", type number}, {"01/09/2019", type number}, {"01/10/2019", type number}, {"01/11/2019", type number}, {"01/12/2019", type number}, {"01/01/2020", type number}, {"01/02/2020", type number}, {"01/03/2020", type number}, {"01/04/2020", type number}, {"01/05/2020", type number}, {"01/06/2020", type number}, {"01/07/2020", type number}, {"01/08/2020", type number}, {"01/09/2020", type number}, {"01/10/2020", type number}, {"01/11/2020", type number}, {"01/12/2020", type number}, {"01/01/2021", type number}, {"01/02/2021", type number}, {"01/03/2021", type number}, {"01/04/2021", type number}, {"01/05/2021", type number}, {"01/06/2021", type number}, {"01/07/2021", type number}, {"01/08/2021", type number}, {"01/09/2021", type number}, {"01/10/2021", type number}, {"01/11/2021", type number}, {"01/12/2021", type number}, {"01/01/2022", type number}, {"01/02/2022", type number}, {"01/03/2022", type number}, {"01/04/2022", type number}, {"01/05/2022", type number}, {"01/06/2022", type number}, {"01/07/2022", type number}, {"01/08/2022", type number}, {"01/09/2022", type number}, {"01/10/2022", type number}, {"01/11/2022", type number}, {"01/12/2022", type number}, {"01/01/2023", type number}, {"01/02/2023", type number}, {"01/03/2023", type number}, {"01/04/2023", type number}, {"01/05/2023", type number}, {"01/06/2023", type number}, {"01/07/2023", type number}, {"01/08/2023", Int64.Type}, {"01/09/2023", Int64.Type}, {"01/10/2023", Int64.Type}, {"01/11/2023", Int64.Type}, {"01/12/2023", Int64.Type}, {"01/01/2024", Int64.Type}, {"01/02/2024", Int64.Type}, {"01/03/2024", Int64.Type}, {"01/04/2024", Int64.Type}, {"01/05/2024", Int64.Type}, {"01/06/2024", Int64.Type}, {"01/07/2024", Int64.Type}, {"01/08/2024", Int64.Type}, {"01/09/2024", Int64.Type}, {"01/10/2024", Int64.Type}, {"01/11/2024", Int64.Type}, {"01/12/2024", Int64.Type}, {"01/01/2025", Int64.Type}, {"01/02/2025", Int64.Type}, {"01/03/2025", Int64.Type}, {"01/04/2025", Int64.Type}, {"01/05/2025", Int64.Type}, {"01/06/2025", Int64.Type}, {"01/07/2025", Int64.Type}, {"01/08/2025", Int64.Type}, {"01/09/2025", Int64.Type}, {"01/10/2025", Int64.Type}, {"01/11/2025", Int64.Type}, {"01/12/2025", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Route", "CLLI", "OSP.StartDate", "End.Date", "NRC", "MRC"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}, {"Value", "NETEX.MRC"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"NRC", "MRC"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Date] < Date.From(Date.StartOfMonth(DateTime.LocalNow() )))
in
    #"Filtered Rows"

Thanks,

Jake

[selimovd]

Hey @Anonymous ,

as we don't have you Excel file, we can't reproduce it like that.

It would be easier if you can share the Excel file.

Otherwise what does the error say?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi Denis,

Here is a link to a copy of the Excel tab with any potentially sensitive data replaced:

https://www.dropbox.com/scl/fi/c6yqyxqp2i5plg7ai14l0/PBI-Share-File.xlsx?dl=0&rlkey=ramp23if8yayas5h...

The error wording is:

"OLE DB or ODBC error: [DataFormat.Error] We couldn't parse the input provided as a Date value.. An unexpected exception occurred."


Thanks,
Jake

[Icey]

Hi @Anonymous ,

Please share the file publicly. I have no access.

In addition, it is suggested to check "Column distribution". Then we can clearly know whether there is an error and will not ignore error rows.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey,

I'm not sure why you can't access as the file is set to public view:



Here is the result from checking 'Column Distribution':

Thanks!

[Icey]

Hi @Anonymous ,

Sorry, my mistake. I can access it. And in your scenario, only "Column quality" option is needed to check. With this feature, we can find if there is any errors. "Column distribution" is not needed, which provides a set of visuals underneath the names of the columns that showcase the frequency and distribution of the values in each of the columns.

Back to the topic. I have tested with the file you shared, but can't reproduce your issue. It works on my side. Could you check if there is really nothing wrong in the previous step?

Best Regards,

Icey

[Anonymous · Accepted solution]

Step 1: Lie down
Step 2: Try not to cry
Step 3: Cry a lot.

I managed to fix it though, I copied the exact M code from the other 3 tables that used almost identical Excel tabs (they had one or two extra steps that did redundant things like removing unrelated columns etc) and it magically decided to cease torturing me.

Thanks for your help regardless!
Jake

[Icey]

Hi @Anonymous ,

Glad to here that the issue has been solved. Please accept your reply as the solution.

Best Regards,

Icey