Import files with different files names - Change dynamically files names

[JMSNYC]

Hi,

I read some of your solutions to address the changes of files names in PowerBI, but I cannot transpose any of them to my own issues. Could you please help ? 

1/ File Name

The file is replaced each month by a new file whose suffix (yyyymm) changes. Hence, I have to update manually my code for the new posted file to be recognized. How can I avoid that ?

2/ Excel Sheet Name. In this example, the name of the source file stays the same, but the Sheet name changes for each new file that crushes the previous one. Hence, I have to change manually the code at these 3 different spots. Can it be automatically updated, whatever the Sheet name is ?

Thank you so much in advance for any help.

[Greg_Deckler]

Going to refer you to @ImkeF on this one but a couple of thoughts. 

You could solve this using parameters, that would prevent you from having to update your code.

In the first case, you could probably calculate out the next file by doing something around grabbing the current date and time.

For the second case, I believe that there is a way to grab a sheet by it's "order" instead of its name. There is a way to return the tables (sheets) in an Excel Workbook, https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook

[JMSNYC]

Thank you for your referral 

Re: issue #1: this is not possible, since I have many files listed that I have to take into account

Re: issue #2: will explore based on your link.

Thanks

[ImkeF]

Not sure if I understand your request correctly, but you can use paramters in Power BI that let basically pass variables to your queries. You can edit them in the UI without having to edit the M-code: https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...

"YourCode1" & Parameter1 & "YourCode2"

[JMSNYC]

Thank you Imke for taking the time to answer my questions. Issue is that I am pretty new at PowerBI and that I do not understand your recommendation about using parameters:

My first request is about this issue:

Today, all my queries/transformations for this file is based on this file name.

By Feb. 1, 2019, a new file will be posted on the blob server with the name ... "GERS/SWO_Facteur9_STATHOP_PDV_3647050001000102_M201901 txt" ... and I will need to use this new file to run the previous transformations. 

TODAY: in order to do that, I get into the "Advanced Editor", and manually change 201812 into 201901, and it transforms the data of the 201901 file the same way it did with the 201812 file

MY QUESTION: I need to automate this manual step. Hence, how can PowerBI change this last part of the file name, or does not see this last part of the file name, so it picks the updated file rather than the previous file ?

If I recall, in Excel you were able to transform the file name into "GERS/SWO_Facteur9_STATHOP_PDV_3647050001000102_* txt" ... and it would pick any file with the same first set of characters

My second request is about this issue:

A file - with the same name - is posted weekly on our Azure blob. This issue, is that the name of the only Sheet of the workbook changes randomly ... last week it was "report1548055718808" ... this week it is "report1449053718936" ... hence, it breaks my M-code.

TODAY: in order to address that, I get into the "Advanced Editor", and manually change "report1548055718808" by "report1449053718936" wherever the Sheet name is used (for instance at 3 locations in the M-code)

MY QUESTION: I need to automate this manual step. Hence, how can PowerBI is indifferent of the Sheet Name so it does not break the code ?

I hope that the description of my two issues helps you better understand the issues I am facing.

Thank you for your help

[ImkeF]

Hi @JMSNYC,

1) you can simply filter your filenames after a "Begins With"-string:

 

2) If there's just one sheet in the file, this expression will adress it dynamically, irrespective of its name:

#"Imported Excel"{[Kind="Sheet"]}[Data]

[JMSNYC]

Hi, Imke.

Re: issue #1 : 

Great suggestion. It works perfectly. Thank you so much. Will post that as a solution.

Re: issue #2 : 

There is still a problem in the code:

For whatever reason, row 6 starts with the Sheet Name. Your suggestion helped to replace the "report..." written in red ... but how to avoid for row 6 to start with the Sheet Name ?

Thanks

[ImkeF · Accepted solution]

You can leave that as it is. It's just a variable name (now) and has no connection to your actual data.