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
knagarlmet's avatar
knagarlmet
Frequent Visitor
2 years ago

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'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"