Date for all rows

[Anonymous]

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

[ppm1]

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

[Anonymous]

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

[m_dekorte]

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]

I fixed it, thanks for the help!

[m_dekorte]

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]

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

[Anonymous]

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

[nickvanmaele]

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]

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

[m_dekorte]

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

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]

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!