I created a table from several workbooks that I imported as connections, I have successfully managed to change the source of the workbook to come from SharePoint: Functioning Source Code
Let

Source = SharePoint.Contents(“”, [ApiVersion = 15]),
#”Shared Documents” = Source{[Name=”Shared Documents”]}[Content],
#”Budget and Forecasting” = #”Shared Documents”{[Name=”Budget and Forecasting”]}[Content],
BUDGETS = #”Budget and Forecasting”{[Name=”BUDGETS”]}[Content],
#”Budget_Year 2024″ = BUDGETS{[Name=”Budget_Year 2024″]}[Content],
#”Interest budget” = #”Budget_Year 2024″{[Name=”Interest budget”]}[Content],
#”Analysis FORC 24″ = #”Interest budget”{[Name=”Analysis FORC 24″]}[Content],
#”ITA INT EXP FORC24.xlsx” = #”Analysis FORC 24″{[Name=”ITA INT EXP FORC24.xlsx”]}[Content],
#”Imported Excel Workbook” = Excel.Workbook(#”ITA INT EXP FORC24.xlsx”),
#”Filtered Rows” = Table.SelectRows(#”Imported Excel Workbook”, each ([Name] = “IT01″)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Name”}),
#”Expanded Data” = Table.ExpandTableColumn(#”Removed Columns”, “Data”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”, “Column23”, “Column24”, “Column25”, “Column26”, “Column27”, “Column28”, “Column29”, “Column30”, “Column31”}, {“Data.Column1”, “Data.Column2”, “Data.Column3”, “Data.Column4”, “Data.Column5”, “Data.Column6”, “Data.Column7”, “Data.Column8”, “Data.Column9”, “Data.Column10”, “Data.Column11”, “Data.Column12”, “Data.Column13”, “Data.Column14”, “Data.Column15”, “Data.Column16”, “Data.Column17”, “Data.Column18”, “Data.Column19”, “Data.Column20”, “Data.Column21”, “Data.Column22”, “Data.Column23”, “Data.Column24”, “Data.Column25”, “Data.Column26”, “Data.Column27”, “Data.Column28”, “Data.Column29”, “Data.Column30”, “Data.Column31”})

in
#”Expanded Data”

What I am trying to do now, is create parameters I can use to replace the Source and the File Path to make them dynamic, as I am importing Data from several dozens of workbooks and sheets.

I have managed to create a parameter for the URL, which looks as follows:
let

Source = SharePoint.Contents(SharePointURL, [ApiVersion = 15]),
#”Shared Documents” = Source{[Name=”Shared Documents”]}[Content],
#”Budget and Forecasting” = #”Shared Documents”{[Name=”Budget and Forecasting”]}[Content],
BUDGETS = #”Budget and Forecasting”{[Name=”BUDGETS”]}[Content],
#”Budget_Year 2024″ = BUDGETS{[Name=”Budget_Year 2024″]}[Content],
#”Interest budget” = #”Budget_Year 2024″{[Name=”Interest budget”]}[Content],
#”Analysis FORC 24″ = #”Interest budget”{[Name=”Analysis FORC 24″]}[Content],
#”ITA INT EXP FORC24.xlsx” = #”Analysis FORC 24″{[Name=”ITA INT EXP FORC24.xlsx”]}[Content],
#”Imported Excel Workbook” = Excel.Workbook(#”ITA INT EXP FORC24.xlsx”),
#”Filtered Rows” = Table.SelectRows(#”Imported Excel Workbook”, each ([Name] = “IT01″)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Name”}),
#”Expanded Data” = Table.ExpandTableColumn(#”Removed Columns”, “Data”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”, “Column23”, “Column24”, “Column25”, “Column26”, “Column27”, “Column28”, “Column29”, “Column30”, “Column31”}, {“Data.Column1”, “Data.Column2”, “Data.Column3”, “Data.Column4”, “Data.Column5”, “Data.Column6”, “Data.Column7”, “Data.Column8”, “Data.Column9”, “Data.Column10”, “Data.Column11”, “Data.Column12”, “Data.Column13”, “Data.Column14”, “Data.Column15”, “Data.Column16”, “Data.Column17”, “Data.Column18”, “Data.Column19”, “Data.Column20”, “Data.Column21”, “Data.Column22”, “Data.Column23”, “Data.Column24”, “Data.Column25”, “Data.Column26”, “Data.Column27”, “Data.Column28”, “Data.Column29”, “Data.Column30”, “Data.Column31”})

in
#”Expanded Data”

My issue now, is that I can’t manage to make a parameter for the filepath, so everything starting at and until:
#”Shared Documents” = Source{[Name=”Shared Documents”]}[Content],
#”Budget and Forecasting” = #”Shared Documents”{[Name=”Budget and Forecasting”]}[Content],
BUDGETS = #”Budget and Forecasting”{[Name=”BUDGETS”]}[Content],
#”Budget_Year 2024″ = BUDGETS{[Name=”Budget_Year 2024″]}[Content],
#”Interest budget” = #”Budget_Year 2024″{[Name=”Interest budget”]}[Content],
#”Analysis FORC 24″ = #”Interest budget”{[Name=”Analysis FORC 24″]}[Content],
#”ITA INT EXP FORC24.xlsx” = #”Analysis FORC 24″{[Name=”ITA INT EXP FORC24.xlsx”]}[Content],
#”Imported Excel Workbook” = Excel.Workbook(#”ITA INT EXP FORC24.xlsx”),
#”Filtered Rows” = Table.SelectRows(#”Imported Excel Workbook”, each ([Name] = “IT01”)),

So far I managed to get this:
enter image description here

However, I then get this gave me an unknown identifier error code, after more experimenting I finally got to this code:
let
Source = SharePoint.Contents(SharePointURL),

NavigateToFolder = Source{[Name="Documents"]}[Content]{[Name="Budget and Forecasting"]}[Content]{[Name="BUDGETS"]}[Content]{[Name="Budget_Year 2024"]}[Content]{[Name="Interest budget"]}[Content]{[Name="Analysis FORC 24"]}[Content],

FileContent = NavigateToFolder{[Name="ITA INT EXP FORC24.xlsx"]}[Content],

ImportedExcel = Excel.Workbook(FileContent)

in
ImportedExcel

And now my PowerQuery is stuck on a constant refresh…
I feel like I’m going in the wrong direction, what am I doing wrong?