cancel
Showing results for 
Search instead for 
Did you mean: 
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!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Sarr
Advocate III
Advocate III

@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 @Sarr, 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)

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!