Forum Discussion
Helper IIIImport files with different files names - Change dynamically files names
You can leave that as it is. It's just a variable name (now) and has no connection to your actual data.
Community ChampionGoing 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
JMSNYCThank 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-bi-desktop-part-4/
"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