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
Anonymous
Not applicable

Date for all rows

Hello,

 

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,

Sjouke

10 REPLIES 10
ppm1
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.

https://www.youtube.com/watch?v=VEjsze31MPU

 

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).

https://www.youtube.com/watch?v=IVMdg16yBKE

 

Pat

 

Microsoft Employee
Anonymous
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?

Anonymous
Not applicable

I fixed it, thanks for the help!

Hi @Anonymous 

 

Give something like this a go

let
    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 
    )
in
    AddDate

 

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

Anonymous
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

Anonymous
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

nickvanmaele
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.

Anonymous
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

m_dekorte
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

m_dekorte_0-1683363955694.png

 

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]]

m_dekorte_1-1683364117094.png

 

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"})

m_dekorte_2-1683364269666.png

 

OR

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]

m_dekorte_3-1683364652614.png

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

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors