Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Date for all rows



I have a folder with multiple files, everyday there comes a new file in the folder. (it is a summary with all the production numbers each day).


Each file contains the date of that day in a cell, but i want to have that date after each row. So that i can filter in my dashboard by each day. i have tried many things, but i can't figure it out.


Thanks in advance.


Kind regards,


Solution Sage
Solution Sage

Does your filename include the date? If so, it might just be easier to parse it out of that and convert to a date.  If not, you may be able to use this shift and fill technique to get the date on every row.


Also, if your refresh slows down, it is also possible to leverage incremental refresh in your published dataset (so you are not reprocessing every file ever time).




Microsoft Employee
Not applicable

My file name includes the date, but how do i parse it out and convert it to a date?

Hi @Anonymous,


Does it have a fixed position in the file name?

And/or can you share a representative example of these file names?

Not applicable

I fixed it, thanks for the help!

Hi @Anonymous 


Give something like this a go

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLslMLS5JLMksBjKyU/MU8hITixRSK5JTcxSMDIyMdQ1MdQ1MdA3MdQ0MdI0M9SpyiiuUYnVI0W1KkW4zdN2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    AddDate = Table.AddColumn(Source, "Date", each 
            v = Splitter.SplitTextByCharacterTransition( {"A".."z", " "}, {"0".."9"})([Name]){1}?, 
            r = try Date.From( Text.Start(v, 10), "nl-NL") otherwise null
        ][r], type date 


Ps. If this helps solve your query please mark this post as Solution, thanks!

Not applicable

data 2.pngdata.png


It looks like this. The data is very messy, i have to clean it up. But this is for the example

Not applicable

data 2.pngdata.png


It looks like this. The data is very messy, i have to clean it up. But this is for the example

Advocate II
Advocate II

Hi @Anonymous ,

Can you include a screenshot of where your date cell is, and where your production data table starts?


Also, are the data on just one worksheet in your file?


@m_dekorte I like your first method, but using the 'date created' from the Windows metadata works only if the file is created on Sjouke's folder on the same day as the production data refer to. This may not always be true, e.g., there could be a lag of a day. Probably Sjouke wants to extract the cell from Excel itself, ie., your second method.

Not applicable

The date is in the name and in a very random place at the bottom of the sheet. see the screenshots

data 2.pngdata 3.pngdata.png

Super User
Super User

Hi @Anonymous 


I'm guessing you've used the Folder connector and selected Combine & Transform Data

And you have a set of helper queries like this



Create a new query again using the Folder connector but this time select Transform Data

Select the columns you want to keep, I've used projection to keep these cols: [[Date created], [Content]]



Now you can invoke the Transform file custom function query on the Content column

Table.ReplaceValue(Source,each [Content],each #"Transform File"([Content]),Replacer.ReplaceValue,{"Content"})




If the date you require is in the Content table and has a fixed place (row and column), try this:

  1. Add a Custom Column
  2. Bring in the Content table: [Content]
  3. Refer to the zero based row number: {2}
  4. And the field name that contains the date: [Company Name]


Now when you expand the Content column it will have that value on each row.


Ps. If this helps solve your query please mark this post as Solution, thanks!


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.