Excel power query [DataFormat.Error] We couldn't parse the input provided as a Date value.

[ajm]

folks, importing multiple template files into one combined file using power query. getting [DataFormat.Error] We couldn't parse the input provided as a Date value. I have been through each of the import files and the date format in each is correctly set to locale (the first choice with the asterisk). I can import each file, one at a time without issue. the error arises when i am attempting to filter the date column for the most recent date only. I have tried changeing type with locale and still get the same error. what else can i detail to aid the solution?

 

[Alex Blakenburg]

Do you have a column with the import source reference eg which file/sheet ?
Is the error occurring on the the Changed Type step ?
Unless you can see an "Error" row just be scrolling down then I would try to find some examples rows using the following:-

• Assuming it is at the changed type step, go to the step before it and add an index column starting at 1.
• At the Changed Type step insert a step Home > Keep Rows > Keep Errors
• Note the index number of a few of the errors and go back to the previous index step and view the value in the rows with the index numbers to see what the issue is with the date field on those rows.

 

[ajm]

ill give that a try Alex. Thanks. i will let you know what happens

 

[ajm]

Do you have a column with the import source reference eg which file/sheet ?
Is the error occurring on the the Changed Type step ?
Unless you can see an "Error" row just be scrolling down then I would try to find some examples rows using the following:-

Alex, there are four rows of data in the first file in the folder.

= Table.TransformColumnTypes(#"Promoted Headers",{{"01.11.21 EOD SBVL .xlsx", type text}, {"Name of Stock Storage Location", type text}, {"Stock Balance Date", type date}, {"Authorised Contact Person", type text}, {"Vaccine", type text}, {"Batch", type text}, {"Comments", type text}, {"Usable stock on hand at end of day:#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock in quarantine at end of day#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock received INTO storage location#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer INTO Type", type text}, {"Source of transfer", type text}, {"Stock transferred OUT OF hub#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer OUT OF Type", type text}, {"Stock destination", type text}, {"Downtime Administered Today#(lf)#(lf)(Vials)", Int64.Type}, {"Downtime Administered Today#(lf)#(lf)(Doses)", Int64.Type}, {"Significant wastage event#(lf)#(lf)Vials", Int64.Type}, {"Significant wastage type", type text}, {"Wastage Comments", type text}})

at this point, i can see the first four rows of data and all subsequent rows show as null for each field. The first column is the file name. selecting the filter on Stock Balance Date, and choosing any date, i see the message "List may be incomplete". if i attempt to "Load more", i then get the [DataFormat.Error] We couldn't parse the input provided as a Date value. then clicking on the OK button, only the first file appears in the Power Query Editor window, with the first four lines of data. The fifth line is Error in each column.

clicking on Error, results in:

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
Stock Balance Date

the query up to this point looks like this:

let
    Source = Folder.Files("C:\Users\moulda\Desktop\Man Subs 20211101"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Stock Location Information - place where stock is stored overnight", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Stock received by storage location (do not use for transfers to clinics where the remainder is returned to the storage location at the end of day)", type any}, {"Column10", type text}, {"Column11", type text}, {"Stock transferred out of storage location (do not use for transfers to clinics where the remainder is returned to the storage location at the end of day)", type any}, {"Column13", type text}, {"Column14", type text}, {"Vaccine administered during downtime (required for doses not recorded online)", type any}, {"Column16", type any}, {"Significant wastage event (greater or equal to 5 vials wasted)", type any}, {"Column18", type text}, {"Column19", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"01.11.21 EOD SBVL .xlsx", type text}, {"Name of Stock Storage Location", type text}, {"Stock Balance Date", type date}, {"Authorised Contact Person", type text}, {"Vaccine", type text}, {"Batch", type text}, {"Comments", type text}, {"Usable stock on hand at end of day:#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock in quarantine at end of day#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock received INTO storage location#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer INTO Type", type text}, {"Source of transfer", type text}, {"Stock transferred OUT OF hub#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer OUT OF Type", type text}, {"Stock destination", type text}, {"Downtime Administered Today#(lf)#(lf)(Vials)", Int64.Type}, {"Downtime Administered Today#(lf)#(lf)(Doses)", Int64.Type}, {"Significant wastage event#(lf)#(lf)Vials", Int64.Type}, {"Significant wastage type", type text}, {"Wastage Comments", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Stock Balance Date] = #date(2021, 11, 1))),

 

[Alex Blakenburg]

Can you post an XL2BB of the problem file with some valid & invalid rows.

 

[rjmdc]

i had this problem yesterday and removed rows to keep errors before any date time number sorts
that means i did this at the step before the couldnt parse...
of course i had the source sheets names so i knew exactly what to correct
i had 30 files in a folder and the errors filrter and source clued me in

 

[ajm]

i had this problem yesterday and removed rows to keep errors before any date time number sorts
that means i did this at the step before the couldnt parse...
of course i had the source sheets names so i knew exactly what to correct
i had 30 files in a folder and the errors filrter and source clued me in

its strange. i have run the get data repeatedly, selecting the next file in the folder each time to base the columns on. each time, it errors out at the first blank row in the first file in the folder. as a test on the date field itself, i imported each file by itself and successfully applied the date filter. so, it has to have something to do with the combine part of the query.

Alex, i will post a pic next.

 

[ajm]

Alex, can't figure out how to use XLBB while the query window is open. i have used a screen capture tool to grab images instead.

So, after selecting the folder, then selecting combine and transform. first file is sample file and the highlighted sheet exists in all workbooks.

clicking ok

=Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Stock Location Information - place where stock is stored overnight", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Stock received by storage location (do not use for transfers to clinics where the remainder is returned to the storage location at the end of day)", type any}, {"Column10", type text}, {"Column11", type text}, {"Stock transferred out of storage location (do not use for transfers to clinics where the remainder is returned to the storage location at the end of day)", type any}, {"Column13", type text}, {"Column14", type text}, {"XXXXXX administered during downtime (required for doses not recorded online)", type any}, {"Column16", type any}, {"Significant wastage event (greater or equal to 5 vials wasted)", type any}, {"Column18", type text}, {"Column19", type text}})[/]

then i select "Use First Row as Headers"

[code]= Table.TransformColumnTypes(#"Promoted Headers",{{"01.11.21 EOD SBVL .xlsx", type text}, {"Name of Stock Storage Location", type text}, {"Stock Balance Date", type date}, {"Authorised Contact Person", type text}, {"XXXXXX", type text}, {"Batch", type text}, {"Comments", type text}, {"Usable stock on hand at end of day:#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock in quarantine at end of day#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Stock received INTO storage location#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer INTO Type", type text}, {"Source of transfer", type text}, {"Stock transferred OUT OF hub#(lf)#(lf)Whole, unopened vials", Int64.Type}, {"Transfer OUT OF Type", type text}, {"Stock destination", type text}, {"Downtime Administered Today#(lf)#(lf)(Vials)", Int64.Type}, {"Downtime Administered Today#(lf)#(lf)(Doses)", Int64.Type}, {"Significant wastage event#(lf)#(lf)Vials", Int64.Type}, {"Significant wastage type", type text}, {"Wastage Comments", type text}})

next step is to apply a filter to the Stock Balance Date column. we select the latest date available.

If Load more is selected, the DateaFormat.Error comes up

If 1/11/2021 (or any date) is selected (null is deselected), the Error line appears after the filtered data

If click on "Error", the DataFormat.Error comes up

The source data (each of the files in the target folder) is in a table. Could this have anything to do with the problem? The table is 1000 rows long.

I'll try to get two instances of excel going to see if i can get xlbb working with power query.

 

[Alex Blakenburg]

XL2BB won't work with Power query, I was hoping for XL2BBs for 2 files (sample data) from the source files so I can try and fix it for you.

Can you show me the Steps for the Query "Transform Sample File" and if it has a Promote Headers line what the column headings are "before" the promote headings step.

 

[Alex Blakenburg]

Without having the above information and based on your screenshot, try
in "Transform Sample File"
(create a backup of your file first)

• Remove the Promote heading step
• add the step to Remove Rows > Remove Top Rows > enter 1 row (or as many rows as you need to put the real headings on row 1)
• now promote the real headings

In the Main Subs 20211101 query, see how it looks at the step "Expanded Table".
If it looks ok that point, I suspect you will need to delete the steps after that and recreate those.

Background Info only for others:
It looks to me that you went Get Data > From File > From Folder and then Combine & Transform Data