Forum Discussion

knagarlmet's avatar
knagarlmet
Frequent Visitor
2 years ago

Help with linking to Sharepoint folder

Hi   I have a model linked to a Sharepoint folder with multiple Excel files in the same format - essentially financial data extracted on a monthly basis is added to a folder by another department (...
Need Help
Tips and Tricks
MFelix's avatar
MFelix
Super User
2 years ago

Hi knagarlmet ,

 

Looking at the images nothing seems different, however there could be some invisible caracters on column A that makes PBI picks up those values. Try to connect to each one individually and check what is the code that is picked up and if you can share it would be great.

knagarlmet's avatar
knagarlmet
Frequent Visitor
2 years ago

Thanks for you help with this.

 

Pulling through from column A in Excel:

 

let
Source = Excel.Workbook(Web.Contents("https://londonmet.sharepoint.com/sites/PlanningandInsight/E5%20Transactions/GL/e5_Trial_Balance_with_transaction_detail_FY2016_periods_1%20to%206.xlsx"), null, true),
#"Report 1_Sheet" = Source{[Item="Report 1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Report 1_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type any}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type any}, {"Column29", type any}})
in
#"Changed Type"

 

Pulling through from column B in Excel:

 

let
Source = Excel.Workbook(Web.Contents("https://londonmet.sharepoint.com/sites/PlanningandInsight/E5%20Transactions/GL/e5_Trial_Balance_with_transaction_detail_FY2023_periods_7%20to%2011.xlsx"), null, true),
#"Report 1_Sheet" = Source{[Item="Report 1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Report 1_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type any}, {"Column28", type any}})
in
#"Changed Type"