Forum Discussion
Replacing data sources to share point folder and file
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:
- Open the original query (OneDrive path) → Go to Advanced Editor.
- Copy everything.
- Create a new query using Get Data → SharePoint folder or Web, as needed.
- Open that query’s Advanced Editor and identify the new Source step.
- Paste the old query, but replace the Source line with the new SharePoint source.
- 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
Super UserHey 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:
- Open the original query (OneDrive path) → Go to Advanced Editor.
- Copy everything.
- Create a new query using Get Data → SharePoint folder or Web, as needed.
- Open that query’s Advanced Editor and identify the new Source step.
- Paste the old query, but replace the Source line with the new SharePoint source.
- 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
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:
- Open your existing query (the one using OneDrive).
- Go to Advanced Editor.
- 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),
- 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