One drive file and folder path to be made dynamic

[monojchakrab]

Hiya good people,

I am working with a couple of folders and the files therein - which are saved on onedrive, syncing between two machines.

But the usernames and onedrive folder path names are different on the two machines - so the query breaks on one of the two machines, if I dont change the file/folder path manually on each machine.

I tried to solve the problem using sharepoint.contents but that makes the loading extremely slow even on fast machine.

Is there anything in PQ like the os.walk in python?

Thanking in anticipation

best regds.,

[christinepayton]

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. 

[monojchakrab]

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.,

[christinepayton]

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.

[monojchakrab]

@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.

[j_ocean]

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.

[christinepayton]

Yes! This is what I do too when it gets really slow, it's worked pretty well for me.

[monojchakrab]

Thanks @j_ocean - I am sorry I could not follow what you said. Could you give some kind of a workflow for the "ETL in PBI service dataflows"? Apologies for not being conversant with this. But seems to be something that could work. Thanks and appreciate

[j_ocean]

@monojchakrab Dataflows are just Power Query Online. You can build your PQ there or even just copy-paste what you have out of Desktop.

Go to Power BI Service (online), to any workspace other than "My Workspace," click "new," and there should be an option for dataflows.

Then Desktop connects to the dataflow and reads in nice clean tables. No processing done on your machine and only the final data goes over your local network.