Connect RowShare to Microsoft Power BI

RowShare is all about securely collecting and sharing data. Although our tables are often visual and intuitive, RowShare is not focused on Data Vizualisation. We do integrate with Business Intelligence / Data Vizualization platforms. This article describes how to pull RowShare data into Microsoft Power BI. A similar approach can be applied to Qlik, Tableau and others...

RowShare does not yet offer a packaged connector for Power BI, but the manual configuration is quite simple. Follow these steps for each RowShare table you want to access from Power BI:

  1. Create and keep at hand the URL that Power BI will access by assembling:
    • https://my.rowshare.com/api/row/loadforparent/
    • and your RowShare Table ID: the part of your table URL that is after the last /.
    For example, for the table https://my.rowshare.com/t/01217d610a19436399c553651cc93063, The Table ID is01217d610a19436399c553651cc93063and the URL PowerBI will access is:https://my.rowshare.com/api/row/loadforparent/01217d610a19436399c553651cc93063
  2. In PowerBI Desktop, add a new Web Data Source:
  1. Copy the URL you created in step 1
  2. If the table is not publicly available, you need to enter your RowShare credentials in PowerBI. Choose Basic Authentication, enter your RowShare email and password, and click Connect.
    If you usually login to RowShare by clicking on the Microsoft or Google icons, you will need to set up a RowShare password to access your same RowShare account. More information here.
  3. On the left pane, right-click on the query, then on Advanced Editor
  4. In the Advanced Editor, replace the 4 lines of the existing Power Query with the code below
    let
    // Load the table as JSON from the URL : "https://my.rowshare.com/api/row/loadforparent/" + the end of the table URL
    RowShareTableAsJSON = Json.Document(Web.Contents("https://my.rowshare.com/api/row/loadforparent/01217d610a19436399c553651cc93063")),
    RowShareTableConvertedToTable = Table.FromList(RowShareTableAsJSON, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Select the actual columns of the table, in the field "Values"
    RowShareTableExpanded = Table.ExpandRecordColumn(RowShareTableConvertedToTable, "Column1", {"Values"}),
    RowShareTableColumns = Table.ExpandRecordColumn(RowShareTableExpanded,"Values",Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(RowShareTableExpanded,"Values") ,each _ <> "" and _ <> null))),
    Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(RowShareTableExpanded,"Values") ,each _ <> "" and _ <> null))))
    in
    RowShareTableColumns
  5. Replace the table ID (highlighted in the code sample above) by your own RowShare table ID (the last part of its URL, after the last /) and hit Done.
  6. Your data source is now configured. You can already see a preview in the Power Query Editor:
  7. Close and Apply. You are now ready to build reports that pull live data from your RowShare table!

Do not hesitate to share with us any comments or feedback about this integration.

Our Professional Service team can also create custom reports for you if needed.

Date columns come with a special formatting that doesn't allow Power BI to show them as dates. If you are familiar with PowerBI, you should be able to adapt the following to your data. You need to do this once for each date column in your RowShare table, and of course replace DateColumn with the name of the column you want to show as proper date.
RowShareTableColumnsWithProperDate = Table.ReplaceValue(RowShareTableColumns,each [DateColumn],each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Int64.From(Text.BetweenDelimiters([DateColumn], "(", "+"))/1000),Replacer.ReplaceValue,{"DateColumn"})


How did we do?


Powered by HelpDocs (opens in a new tab)