Help with linking to Sharepoint folder

[knagarlmet]

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 (hence not being able to share). However, PBI Desktop reads some files from cell A2 and others from B2. I cannot see any logical reason for this - they are extracted from the same system with the same format, there are no named ranges, etc.

Any advice?

[knagarlmet]

Thanks for your response. Yes, PBI is skipping data in A2 (or in this case skipping column B). 

As mentioned in my initial comment, I believe they are formatted in the same way as they are extracted from the same system. The first image below is coming through correctly, but the second isn't. As I mentioned, no named ranges, etc. in the Excel files. I was wondering if others were having a similar issue and if there was a simpler fix than having to transform.

This one is coming through correctly:

This one isn't:

[MFelix]

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]

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..."), 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..."), 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"

[MFelix]

Hi @knagarlmet ,

When you refer that the PBI reads some files from A2 and other from B2 does that mean that on some files the information starts on A2 and on other on B2 or is PBI skipping some data on files that have data on A2?

For making this type of automated transformation based on Folders (sharepoint or others) you need to be sure that the files are all formated the same way meaning that they all have the same number of columns, they all start at the same column, etc. If this does not happen you need to add transformation steps that will allow to overcome those differences between the files.