Changing from Local/Network Drive Folder to Sharepoint Folder? OR Changing from Folder to File

[SadakaAli-6486]

Hi everyone. We ran into an issue where other people accessing the report I built are not able to refresh the report with a daily export that is dropped into this destination folder, which I assume is because they may have the network drive mapped differently. For other reasons as well, I want to change this from the current folder path, to a Sharepoint Folder.

Current:

Now here's the weird thing. I created a folder where individual static FILES were linked to the file, and there was no issue:

However, when I try to link to the folder on the sharepoint site, whether I leave the link as it is, or if I chop it down to end right at the folder, I get this error. Full link: https://COMPANYNAME.sharepoint.com/:f:/r/sites/US-COMPANYNAME-FTCTracking/Shared%20Documents/General/North%20American%20Transportation%20Performance/RAW%20DATA?csf=1&web=1&e=8ydxud

Is there any way I can change to this Data Source, or is my only option to create a new Data Source from scratch, directing it to the Sharepoint folder from the get-go? Honestly - an acceptable solution would be if I could change this to a FILE as opposed to a FOLDER, as that setup is no longer needed, but I couldn't figure that out either (seems people usually go FILE to FOLDER, not the other way around).

[johnt75 · Accepted solution]

Create a new query and connect to the Sharepoint file or folder you need, then open Advanced Editor and copy the steps then use that to replace the initial steps in your existing query. You will be able to keep all the existing steps after the connection, e.g. changing data types etc, so everything else should just work. You can then delete the new query you created as you won't need it anymore.

[SadakaAli-6486]

This solution would mean I have to recreate all measures/calculated columns, redirect all relationships in the Data Model to the "new" data, then update all visuals to the fields in the new data, correct?

I was hoping to avoid that since there are 22 pages and many measures/columns associated with this query. I'm okay if this is the case, though - this was the first PowerBI report I built, and to be honest, this wasn't supposed to get so large, but it grew pretty rapidly.

[johnt75]

No, you're misunderstanding me. By editing your existing query to only change the initial steps, to do with the connection to sharepoint rather than a local file, you keep all subsequent steps involving any new columns or anything else. The existing table will be loaded into the model and all relationships, measures, calculated columns etc will be retained. All that is changing is the first few lines of the query where you define the file to connect to.

[SadakaAli-6486]

Ah I see exactly what you mean now. So I've done that, and I've fixed about 4 different issues that popped up likely because of shortcuts I've regrettably taken. However - I am stumped with this error. I cannot figure out for the life of me where to look to get it to recognize the key of the table since it's showing that it's pulling a blank value.

[SadakaAli-6486]

Figured it out - I had to delete the Filtered Hidden Files1 line, and for some reason, my Transform File query was referring to a separate Test query that was still pulling from the original datasource and I believe was actually ultimately where the table was being pulled in from. Once I matched the sharepoint folder in that query, everything worked perfectly. Now anyone in our department is able to refresh the data - thank you so much!