locked
SHarePoint Forms Library data to Power BI RRS feed

  • Question

  • A user has a Forms Library on SharPoit Online and wants to connect that data to Power BI. I read here that to achieve this I need to "go through" Excel:

    https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-a-Sharepoint-Form-Library/td-p/172946

    My question is, how can I get the data from a Forms Library to Excel? I was expecting to use OData: In Excel -> data tab -> Get External Data from other sources -> OData then use an address similar to below

    https://somecompany.sharepoint.com/sites/somedept/Reports/MyList/_vti_bin/listdata.svc

    But that doesn't work. The plan was then to save the spreadsheet to a document library and connect to this via Power BI.

    In addition I need to preeserve the item level permisisons that exist. So forms 1-10 can only be accessed by users A-C and forms 11-20 can only be accessed by users D-F. I was hoping Power BI would preserve that security configuration. If that's not possible I could create multiple Power BI reports or change the permissions on the published dashboard / the pages upon which they are displayed (there are 20+ departments so it would be a bit of a hassle).

    So my first question is - how can I get Excel to connect to my forms library (similar to an OData connection)?

    Tuesday, June 5, 2018 3:55 PM

All replies

  • Hi PetyrBaelish,

    You could export the form library to the excel by clicking “Export to excel” in the ribbon.

    And then it will export the form library as the .iqy file, you could open the file in excel.

    And if you add the new items in the form library, you could refresh in the excel.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, June 6, 2018 6:45 AM
  • Hi Sara,

    I followed your instructions ...

    1. Form Library - Export to Excel -> saved .iqy file to doc library on same site
    2. Opened excel (in client application), file -> open -> selected the iqy file
    3. Chose to insert the data to my existing workfbook as a table (and the data is there)
    4. Saved the spreadsheet to doc library on same site and closed Excel (the modified date of the workbook matches my activity)

    Then when I tried to reopen the workbook, it was blank. I have tried it a couple of times, and it is blank every time I try to reopen it.

    I'm using Excel 2016.

    Any thoughts?

    Thursday, June 7, 2018 3:54 PM
  • Hi PetyrBaelish,

    After you open the .iqy file, then I suggest you could save the excel file to the local computer firstly. And then upload the excel file to the document library.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, June 8, 2018 1:38 AM
  • Hi PetyrBaelish,

    If the reply is helpful to you, you could mark the reply as answer. Thanks for your understanding.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, June 8, 2018 8:51 AM
  • Hi PetyrBaelish,

    I am checking to see how things are going there on this issue. Please let us know if you would like further assistance.

    If the issue was resolved, you can mark the helpful post as answer to help other community members find the helpful information quickly.

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, June 18, 2018 1:55 AM