Unable to connect to get data from share point folder

Microsoft Employee

9 years ago

According to your description above, I just tested it with the latest version of Power BI Desktop(2.48.4792.721 64-bit (July 2017)), and I can connect to sharepoint folder as a data source and use combine binaries to combine the CSVs, and the refresh works all fine without any issue when there is new data from source side. Following is the M Query I used in the Advanced Editor:

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Monthly Data")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Query1", each #"Transform File from Query1"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Query1"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", Int64.Type}, {"Sales", Int64.Type}, {"Category", type text}})
in
    #"Changed Type"

I am not able to load sharepoint folder anymore.

Could you try deleting the data source credentials (File->Options and Settings->Data Source Settings->Clear Permission) and reconnect to see if it works. :smileyhappy:

Regards

desairajvijay
Frequent Visitor
9 years ago
Thank you for your response. I tried "clear permission" but it did not work. I am attaching the screenshot of the process and error if you can help me with it.

Since, it did not allow me to connect to sharepoint folder. I link the folder on my machine and later on I edited in the advace editor and changed it to SharePoint.Files("https://xxxxx.sharepoint.com/xx/xx"). These worked and load all the files from SharePoint team site. However; when I filtered out the folder path for the particular files. I see one error at the end of all the Csvs. It is the same error which appears when I hit close & apply. Screenshots are attached below.

The last row in my data still displayes the above error. After I did combine binaries. Since all the required data were available. I did close and apply to check, and it gave me error shown as below.

I have been doing the same procedure since past year. I never encountered such situation.

Thank you for the help.

Best,

Raj
- v-ljerr-msft
  Microsoft Employee
  9 years ago
  Hi desairajvijay,
  
  The last row in my data still displayes the above error. After I did combine binaries. Since all the required data were available. I did close and apply to check, and it gave me error shown as below.
  
  I'm not sure why there is an error row in your query. It seems to be that there may be a corrupt file on the SharePoint side.
  
  Have you tried removing the error row first, then doing the combine binaries to see if it works? :smileyhappy:
  
  Regards
  - desairajvijay
    Frequent Visitor
    9 years ago
    Thank you for the response. I tried "Remove Errors" before combining binaries. However; it gives me the same error right away in the edit queries page itself.
    
    I don't think there are any corrupt file on sharepoint as I can see all my files of the particular folder when I do filter rows with folder path. All files are displaying correctly but error on the last line.
desairajvijay
Frequent Visitor
9 years ago
Thank you for your response. I tried "clear permission" but it did not work. I am attaching the screenshot of the process and error if you can help me with it.

Since, it did not allow me to connect to sharepoint folder. I link the folder on my machine and later on I edited in the advace editor and changed it to SharePoint.Files("https://xxxxx.sharepoint.com/xx/xx"). These worked and load all the files from SharePoint team site. However; when I filtered out the folder path for the particular files. I see one error at the end of all the Csvs. It is the same error which appears when I hit close & apply. Screenshots are attached below.

The last row in my data still displayes the above error. After I did combine binaries. Since all the required data were available. I did close and apply to check, and it gave me error shown as below.

I have been doing the same procedure since past year. I never encountered such situation.

Thank you for the help.

Best,

Raj