August 5, 2023
Is there a way to monthly update the onedrive location path at power query?
For example, An excel file is connected to a database located in the following onedrive path: htps://my.sharepoint.com/:x:/r/personal/01_BG/2023/BG-03/01_Actual_Cashflow_2023.xlsm
The next month I have to update the source changing the folder name from "BG-03" to "BG-04", so the new source is htps://my.sharepoint.com/:x:/r/personal/01_BG/2023/BG-04/01_Actual_Cashflow_2023.xlsm.
I think to have a defined cell name, called "BGMonth", so I could change and automatically the onedrive path update.
Could you help me with that?
January 31, 2022
Make sure you connect to the named cell with PQ. Right-click inside the 'cell' that has thecae for the current month and select 'Drill down'. That will create a text variable that you use in the file path of the Source step.
Edit the Source step from what you showed above to this:
Excel.Workbook(File.Contents("https://my.sharepoint.com/:x:/.....1_BG/2023/" & BGMonth & "/01_Actual_Cashflow_2023.xlsm"), null, true)