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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a date custom column by file date

Hi everyone,

 

I daily download a .csv file that has no date reference. I'd like to create a custom column that sets the date by the 'file modified date' (or similar).

 

any ideas?

 

Thanks!

1 ACCEPTED SOLUTION

Instead of combining binaries, add a custom column with formula =Csv.Document([Content]).

This will give you a table with the file contents, which can be expanded without losing the "date modified".

The "Content" column can be removed after you added the custom column.

Specializing in Power Query Formula Language (M)

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

OK, this is just a working theory, but if you use a Folder query, you can get the last modified date. So I'm thinking that perhaps creating a custom function where you grabe the "Date Modified" from a folder query wrapped in your function that you could return this back as a value for a custom column in the query. Perhaps @ImkeF has a better way, she is the M code expert.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Anonymous

 

Did you try this?

1. get data

2. select folder option

3. specify the path of the file

4. now you can see the file properties and choosethe field you want and add it as column 

Capture.PNG

5. have that column in the slicer 

 

Anonymous
Not applicable

Hi @Anonymous, thanks for your help

 

I think i'm missing the step where 'i choose the field i want and add it ass column' (and later on, step 5). Usually, when i get a .csv from a folder, i use the 'combine binaries' button, and all of the other columns disappear.

Before combining binariesBefore combining binaries

 

 

After combining binariesAfter combining binaries

 

 

 

 

 

Instead of combining binaries, add a custom column with formula =Csv.Document([Content]).

This will give you a table with the file contents, which can be expanded without losing the "date modified".

The "Content" column can be removed after you added the custom column.

Specializing in Power Query Formula Language (M)

@MarcelBeug, good tip.

 

My new column is containing all of the info. I mean, it does not split into columns. Any ideas how to overcome this?

My file contains Tab separated info

 

Thanks

Anonymous
Not applicable

@MarcelBeug one more thing: what if it was an xls / xlsx document?

That would be very similar: just replace Csv.Document with Excel.Workbook.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Is there also any way to get modified/created date out of file when the file is stored in the SharePoint?

Iam connecting to it with powerbi using get data "web"

Hi,

 

I have the same issue.

I would like to display the creation date of an excel file in my report.

 

Any idea how to read and display it?

 

Thanks a lot for helping.

 

Rob

Anonymous
Not applicable

@MarcelBeug Thanks a lot, that worked just fine!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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