Power Pivot source Data File name changes

I import data from our main data base but when the file is exported it puts a date at the end of the file each time it is exported


I use Power Pivot to import the data.

How do I do a refresh the data when the source data file name is not the same when exporting the next day for eg today source file is ales report_09/07/2019 tomorrow is is Sales Report_10/07/2019 ?

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

There are several ways to handle this. But first, I'd recommend that you don't query/connect to source file directly from PowerPivot data model wizard.

Instead, leverage Get & Transform (PowerQuery) to import data, perform transformation and then load to data model.

This will ensure that data conforms to model's need and ensures consistency of DAX calculation results.

Think of PQ as ETL tool.

Now to deal with date change. There are several ways to do it.

1. Use PQ's 'M' query language to construct file name string dynamically. To get date using PQ...

Please Login or Register  to view this content.

This will generate string "09/07/2019" for today (Jul 09, 2019).
You can then concatenate it to static portion to create query string.

2. Perform calculation in Excel sheet and assign it to Named range. Then pass that on to PQ.
Say in A1 of some sheet, add following formula.

Formula:

Please Login or Register  to view this content.

Name the cell as 'lDate' using Name manager.
Then in Advanced editor of PQ, use following line to pass the value.

Please Login or Register  to view this content.

Then again, use it to construct your string.

There are other methods, but above two would be easiest to set up and maintain.

If you need further help in implementation. I'd recommend that you upload sample source file and sample out put file.