Forum Discussion

vehau1's avatar
vehau1
Helper I
7 years ago

Dataflows: Import from Sharepoint folder

  I want to gather all my dataflows in one app space. Part of this data is an Excel Workbook that's placed in a Sharepoint page that I own.   I want to connect to and import this Excel file as a d...
Need Help
Tips and Tricks
v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

Hi vehau1,

 

There may be occasions when connecting to data sources for dataflows run into issues. This section provides troubleshooting tips when such issues arise.

  • Salesforce connector - Using a trial account for Salesforce with dataflows results in a connection failure with no information provided. To resolve this, use a production Salesforce account or a developer account for testing.

  • SharePoint connector - Make sure you supply the root address of the SharePoint site, without any subfolders or documents. For example, use link similar to the following: https://microsoft.sharepoint.com/teams/ObjectModel.

 

Reference: https://docs.microsoft.com/en-us/power-bi/service-dataflows-create-use.

 

Regards,

Jimmy Tao

vehau1's avatar
vehau1
Helper I
7 years ago

Thank you for the response.

 

Regarding your Sharepoint address, I am aware of this. I am able to use the connector "Sharepoint Online List" to import the list of objects that holds information in place on my Sharepoint Site, providing the URL to the site as you described.

 

However, I can't get the actual files stored on the Sharepoint site unless I use the "Sharepoint Folder" connector

This connector, "Sharepoint Folder", isn't even available in the "Get Data"-area when I try to create a new entity. So I'm stuck.

  • Veles's avatar
    Veles
    Advocate V
    7 years ago

    vehau1

     

    I've managed to find a workaround. Select Blank Query and then enter the below as the source. Click next and you can go from there. You would just enter the URL that you would normally do when you select SharePoint Folder in the desktop version.

     

    Source = SharePoint.Files("https://[YOURSITE].sharepoint.com/sites/[YOUR SITE]/", [ApiVersion = 15])

    It looks like it's a bit cut down though so there's no button to automatically combine files together as you can in desktop.

     

    EDIT: Managed to get the file combination working with a bit of tweaking - I found it easier to create the query and desktop then copy the whole lot over into a blank query.

    • smagbuo's avatar
      smagbuo
      Frequent Visitor
      7 years ago

      I'm doing the same workaround but haven't been successful. I'm copying every query associated to the combine method including the fx (invoke function) since the main query (Query1 - which combines all the files in the specified SharePoint folder) is breaking due to missing referenced module. Could you share here the steps you did? Did you have to copy over all the Sample Queries within Transfor File from Query folder as well?

       

       

       

      • Shelley's avatar
        Shelley
        Post Prodigy
        7 years ago

        Yes, I agree with smagbuo, will you please share how you did this in order to add the Transform Query steps into the dataflow?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Please consider editing to read " I found it easier to create the query in Power BI Desktop, then copy the code from advanced editor over into a blank query."

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Does anyone know if using this method of accessing sharepoint files and pulling them into a dataflow is subject to a size limit?  It seems that when I use this metholdology to access smaller files, manipulate them in a query and then save them as a dataflow it works fine.  But I have a few situations then the transformations require appending and merging data from separate tables.  It seems to save OK, but when I go to refresh the dataflow I get an error similar to this  - makes me think that larger more complex transformations using this metholdology fail for some reason - any thoughts?

        Run timeDataflow nameDataflow refresh statusEntity nameStart timeEnd timeEntity refresh statusError
        24/04/2019 11:20:55ReportFailedReport24/04/2019 11:20:5524/04/2019 11:21:26FailedError: We're sorry  an error occurred during evaluation. Request ID: 0500d1d7-d106-7e47-0987-fa523e7924a7 Activity ID: 4486d774-3b83-4623-9f18-12d17b06e662
  • Veles's avatar
    Veles
    Advocate V
    7 years ago

    Yep, I have the same issue.

     

    I need to combine multiple files in a SharePoint folder which I can do fine in Power BI but there's no SharePoint Folder connector available in dataflows :(

    • uhdacarter's avatar
      uhdacarter
      Frequent Visitor
      4 years ago

      And, 3.25 years later, Combine files from SharePoint Folder is still not an available Connector for Dataflows. (At least, not in Power BI Pro, that I can tell.)

  • graemeg's avatar
    graemeg
    Frequent Visitor
    5 years ago

    Appreciate this post is a few years now.  I was looking to do the same as above.  I solved this, and quickly, setting up the connection in the Power BI desktop and making sure it worked.  I then went into Power Query for the connection and copied the Power Query code from the Advanced Editor into a blank Dataflow.  It worked.