Forum Discussion
One drive file and folder path to be made dynamic
I'm not quite sure what you're looking for here, but if I were pulling files from two different OneDrives, and they had the same columns etc, I would just do one query for each (with no transformations) and then append them together in a new query, then add the transforms there.
You can also use parameters in your query, so you could have a parameter for the site path, but I'm not sure that's useful to you here.
Hey christinepayton - I am sorry I was not able to communicate clearly. Let me attempt again :
1. I have a file called file.xlsx and and a folder which has all the source/raw files. Let us call it sourcefolder.
2. file.xlsx and source folder are inside my onedrive folder
3. The onedrive account is the same on both the computers
4. But the paths are different :
a. One one machine its like c:\users\mmchak\onerdrive--etc & on the other :
b. c:\users\manoj\onedrive<followed by something else>
5. While the onerdrive account is the same, somehow the onedrive folder name shows up as different on the 2 different machines
6. Hence, using a parameter does not help since the onedrive syncs between the 2 machines and what I have as a folder path as parameter on one machine becomes the same for the 2nd one - i) so, it works for one machine on which the file/folder path are correct but not for the other one ii) If I am accessing the same file on the 2nd machine, I need to change the path set as a parameter manually
7. I was looking to make this access dynamic - so that for wach respective machine, the file and folder path are picked up dynamically
Not sure if this clears the air - happy to expound further...
Appreciate and best regds.,
Oh - just change the path that it's looking for the file to the web-synced OneDrive URL. All of the OneDrive folders are synced up to M365, so if you connect to it in the cloud instead of the local copy, it will always be the same.
Here are a couple videos on how to change the source from local to SharePoint (or OneDrive - concept is the same) - one for changing individual file references and one for folder connector references.
christinepayton - that is my current solution actually. But every time I open the query, the files take an eternity to load and furnish. That is why I was looking for a local path solution. But thanks for the leg-up.
We had the same issue and our fix was to do the ETL in PBI Service Dataflows. The ETL stays in the cloud and Desktop only has to pull down the final results at the end through your local internet connection. It's much, much faster both in editing the ETL and building the data model/reports in desktop.
Depending on how complex your flow is you can gain further improvements by chaining two dataflows together, one to extract from the files and the next to crunch the data.
