Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JRParker
Helper III
Helper III

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

In PQ, initiated a New Source from a folder with a host of Excel files with the MMDDYYYY embeded in their file names.

 

After combining and transforming, the Source.Name field includes the MMDDYYYY within the respective Excel file names.  

 

After adding a custom colum to extract the MMDDYYYY (via Text.Middle([Source.Name], 16, 8)), one is left with Custom column Type Any.  The error (in the subject line of this correspondence) results when simply trying to transform to Date.  First transforming to either Text or Whole number doesn't solve the issue;  neither does Trim and/or Clean.  Any ideas?

 

Looked at prior posts related to this subject but they're all slightly different context.  

 

Chat GPT couldn't figure it out. 🙂

Thanks in advance. 

2 ACCEPTED SOLUTIONS

Just tested and that mmddyyyy text string couldn't be parse as a date even if I added a culture. Try this custom column

let 
dt = Text.BetweenDelimiters([Column1], " ", ".x"),
yr = Number.From(Text.End(dt, 4)),
mo = Number.From(Text.Start(dt, 2)),
dy = Number.From(Text.Range(dt,2,2))
in #date(yr, mo, dy)

danextian_0-1691617785490.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

10 REPLIES 10
danextian
Super User
Super User

It is possible that the date starts at 15 and not at 16 or any other number.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Just tested and that mmddyyyy text string couldn't be parse as a date even if I added a culture. Try this custom column

let 
dt = Text.BetweenDelimiters([Column1], " ", ".x"),
yr = Number.From(Text.End(dt, 4)),
mo = Number.From(Text.Start(dt, 2)),
dy = Number.From(Text.Range(dt,2,2))
in #date(yr, mo, dy)

danextian_0-1691617785490.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

thank you! ... finally took the time to figure the M code in the context of my existing query....here is the tail end of the query:

 

#"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "dt", each Text.BetweenDelimiters([Source.Name], " ", ".x")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "yr", each Number.From(Text.End([dt],4))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "mo", each Number.From(Text.Start([dt], 2))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "dy", each Number.From(Text.Range([dt],2,2))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Date", each #date([yr],[mo],[dy])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom4",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"dt", "yr", "mo", "dy"})
in
#"Removed Columns"

 

....data exported from table visual:

 

Date
3/21/2022
3/31/2022
4/30/2022
5/31/2022
6/30/2022
7/31/2022
8/31/2022
9/30/2022
10/31/2022
11/30/2022
12/31/2022
1/31/2023
2/28/2023
3/31/2023
4/30/2023
5/31/2023
6/30/2023
7/31/2023

I don't know enough about M language to embed what you have into my existing query.  This is what I have so far... can you help with embedding?

 

let
Source = Folder.Files("C:\FVE TB Folder"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)")))
in
#"Expanded Table Column1"

Thanks Danextian, have confirmed the date starts at 16, and the result of the custom field is for example '01312023', extracted from the value 'GL_TrialBalance 01312023.xls' from Source.Name column. 

 

Perhaps it would be best to create a Date custom field in one fell swoop; extract and format at the same time, rather than creating the custom field of characters and then trying to format?

Ritaf1983
Super User
Super User

Hi @JRParker 

To make it easier to understand and work with, please share your table.

 

When getting data from a folder of Excel workbooks, regardless of the underlying structure of the workbooks within the folder, the very first couple of PQ steps are columns of meta data including a source column of the workbook names.  The file name naming convention of the underlying workbooks withing the folder is critical in that a MMDDYYYY is embedded into each file name.  For example, "FILE05312023", "FILE06302023", etc.  The intent of this thread was to find a way to extract the MMDDYYYY out of each file name and include this Date in each row of the respective workbooks in the folder. 

 

Along with the meta data columns is the Table column which is each workbook to be expanded to include all the underlying fields .  Below is a sample where all the meta data columns have been removed other than the Source and table columns:

SourceTransform File
FILE01312023Table
FILE02282023Table
FILE03312023Table
FILE04302023Table
FILE05312023Table
FILE06302023Table


The query would look like this after expanding the table:

 

SourceField 1Field 2Field 3
FILE01312023value 1value 1value 1
FILE01312023value 2value 2value 2
FILE01312023value 3value 3value 3
FILE02282023value 4value 4value 4
FILE02282023value 5value 5value 5
FILE02282023value 6value 6value 6
FILE03312023value 7value 7value 7
FILE03312023value 8value 8value 8
FILE03312023value 9value 9value 9
FILE03312023value 10value 10value 10
FILE04302023value 11value 11value 11
FILE05312023value 12value 12value 12
FILE05312023value 13value 13value 13
FILE05312023value 14value 14value 14
FILE06302023value 15value 15value 15
FILE06302023value 16value 16value 16

 

The intent is to add a custom column by extracting the date from the Source column:

 

SourceField 1Field 2Field 3Date
FILE01312023value 1value 1value 11/31/2023
FILE01312023value 2value 2value 21/31/2023
FILE01312023value 3value 3value 31/31/2023
FILE02282023value 4value 4value 42/28/2023
FILE02282023value 5value 5value 52/28/2023
FILE02282023value 6value 6value 62/28/2023
FILE03312023value 7value 7value 73/31/2023
FILE03312023value 8value 8value 83/31/2023
FILE03312023value 9value 9value 93/31/2023
FILE03312023value 10value 10value 103/31/2023
FILE04302023value 11value 11value 114/30/2023
FILE05312023value 12value 12value 125/31/2023
FILE05312023value 13value 13value 135/31/2023
FILE05312023value 14value 14value 145/31/2023
FILE06302023value 15value 15value 156/30/2023
FILE06302023value 16value 16value 166/30/2023

 

...then remove the Source column and proceed with addtional query steps.... hope this clarifies.

My style, when connecting to a folder, is to make sure that I am getting data from the right files so I apply filters to the file name (contains or starts with a particular string and doesn't contain $ as this is a temporary file) and the extension. I also keep the filename so I know which file my data is from. Very helpful when I expect some data for a column or more with blank rows  or when I expect this x count of files to be loaded but some are missing.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Interesting.... I've never thought of importing files from a folder with various files, file types; I always set up a folder/subfolder exclusively for the imported files. 

That's just foolproofing which files to get. I or someone else might erroneously save a non-relevant file in those folders.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.