Forum Discussion
Import 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.
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
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"
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
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]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