Replacing data sources to share point folder and file

[whatisok]

I have many Power BI reports that uses one drive paths as data sources, and I want to replace all the data sources to sharepoint folder or individual file, so I can have scheduled refreshes on power BI service.

I did this jus for one of the reports.  Here's how I did it.

As for individual file in a sharepoint folder, I did Get Data -> Web and pasted the url of the file.

As for a sharepoint folder, I did Get Data -> Share Point folder and paste the site url and then filter by folder path in Power Query Editor.

Then in Power Query Editor, I manually applied all the same steps that were taken for previous version of the same data (same file but using one drive path).  Then, I deleted the previous version of the same table, loaded the new table, and recreated all the measures that I had createad for the older version.

Manually copying every step for the new data table took too long.

Can I just replace the Source from the steps taken?  Will it work without breaking?  Is the answer different when I am connecting to a share point folder vs a single file in a share point folder?

Thank you

[Nasif_Azam · Accepted solution]

Hey @whatisok ,

Yes, you can replace the Source step in Power Query using Advanced Editor rather than manually recreating all the steps. This is much faster and safer if done carefully. Power Query steps are sequential. If you only replace the Source step, and the structure of the new file (columns, names, etc.) is the same, the rest of the steps should continue to work as long as the name of the step being replaced remains the same.

Steps Should Follow:

1. Open the original query (OneDrive path) → Go to Advanced Editor.
2. Copy everything.
3. Create a new query using Get Data → SharePoint folder or Web, as needed.
4. Open that query’s Advanced Editor and identify the new Source step.
5. Paste the old query, but replace the Source line with the new SharePoint source.
6. Make sure that all step references are preserved (i.e., don’t change step names like Source unless you update every reference to it).

Original OneDrive Source:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive\myfile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}})
in
    #"Changed Type"

New SharePoint Source:

let
    Source = Excel.Workbook(Web.Contents("https://mycompany.sharepoint.com/sites/mysite/Shared Documents/myfile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    ...

Replace the first Source line only, and keep the rest of the steps untouched, assuming the structure is the same.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[whatisok]

Thanks! This makes sense.  Instead of copying the M code from OneDrive source to SharePoint Source, is there any way to copy from SharePoint source to the OneDrive source (just replace the source part)?  The way you suggested is good, but I would still need to delete the old OneDrive source and once I load the SharePoint source table I would have to recreate all the measures I previous had in the OneDrive source table.

[Nasif_Azam]

Hey @whatisok ,

Great follow-up question!

Yes, you can absolutely do it the other way around by replacing just the Source step within the existing OneDrive-based query, you can preserve everything else: transformations, column references, and even all your DAX measures tied to the table name.

Steps Should Follow:

1. Open your existing query (the one using OneDrive).
2. Go to Advanced Editor.
3. Replace just the Source = ... line with the SharePoint version:
   • Source = Excel.Workbook(Web.Contents("https://yourcompany.sharepoint.com/sites/yourSite/Shared Documents/yourFile.xlsx"), null, true),
4. Keep all the following steps unchanged—as long as the data structure (sheet name, column names/types) is the same, it should work seamlessly.

This way you won't have to delete the original table, and all relationships, visuals, and measures stay intact.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Cookistador]

The easiest way to do that

It is by using advanced editor in Power query

You have to check what is the different in the applied steps

To do that, in Power query  in home, select advanced editor

You should see something like:

let
Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxxxxxx\Downloads\BrewersProduction_2023.xlsx"), null, true),
BrewersProductions_Sheet = Source{[Item="BrewersProductions",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(BrewersProductions_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department of the Treasury", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}})
in
#"Changed Type"

You can replaced the first step by the steps for your sharepoint folder,

You just have to be careful when you replaced something with the previous name

In the name of your step, the previous step must be mentionned

#"Promoted Headers" = Table.PromoteHeaders(BrewersProductions_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department of the Treasury", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}})

If you need some suppport, you can share the advanced editor code of one sharepoint file and one onedrivefiles (with the steps applied on it)

Kind regards

[whatisok]

So, just replace the old path with new SharePoint file path?

I am not sure if I understand this completely.   I won't have any steps for my new data source.  I will just want it to go through the same steps as the previous version.  Also, how does it work with Folder? 

[Cookistador]

Indeed,
Copy the first lines from Sharepoint transformation (you need the step where you extract the values from the csv)
Once you have your raw files in the table, copy the first lines and replace them by the first lines in your one drive table