Date for all rows

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

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!

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.