Visualizing Braineet projects in Microsoft Power BI & Power Query

This article outlines a solution to efficiently and automatically bring information from your Braineet portfolio into Microsoft Power BI and Microsoft Power Query in Excel.

What is Power BI & Power Query and how can I use it with Braineet?

Power Query is Microsoft’s data connection tool that enables users to connect, combine, and refine data sources. These connections can come from self-hosted and/or static files like XLSX, CSVs, XML files, as well as cloud-based sources such as Azure SQL Databases and Salesforce.

Currently, Power Query is only available in the Windows versions of Excel and Power BI. Using Power Query, we can pull projects from Braineet into Excel or Power BI so that teams can create complex dashboards, and enrich Braineet data with other data sources available in Power BI and Microsoft Excel.

What will I need?

A computer running Windows
Power BI Desktop or Microsoft Excel 2016 (or later)
A Braineet portfolio access with manager permission, and a API key.
The below query in Microsoft's M formula language

📢 You need to have at least 1 project on your portfolio

Step-by-step instructions

The process for connecting Braineet to Microsoft Power BI and Excel (Power Query) are very similar. In this section, we'll cover how to use the Braineet API to connect to both.

1. Copy Template M Query

We've developed the following template M Query that should help you get started bringing data from a table in an Braineet portfolio to a Power BI Query. This query uses Query Parameters to define your Braineet PORTFOLIO_ID , PLATFORM_URL , as variables. After we set the parameters in the next steps, you should not need to modify the query directly.

let
    TablesWithDate = Table.FromList(Json.Document(Web.Contents(PLATFORM_URL, [RelativePath = "/api/rest/portfolio/" & PORTFOLIO_ID & "/projects"]))[data],
             Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RecordTable = TablesWithDate[Column1],
    GetRecordFields = List.Distinct(List.Accumulate(RecordTable, {}, (s,c)=> s&Record.FieldNames(c))),
    CombinedTableExpanded = Table.ExpandRecordColumn(TablesWithDate, "Column1",GetRecordFields, GetRecordFields)
in
    CombinedTableExpanded

2. Create a Query

Create a new Power BI document and select Get Data from the Home toolbar. In the dropdown menu, select Blank Query to open the Power Query Editor.

3. Create Parameters

In the toolbar of the Power Query Editor, select Manage Parameters. This will open the window where you can create and edit Power Query Parameters.

This is where you will create and define the parameters referenced in the template :

PORTFOLIO_ID : ID of the portfolio

PLATFORM_URL : URL of your platform

The URL & the ID can found on your portfolio:

For exemple: https://demo-ikea.braineet.com /api/rest/portfolio/616405cd254ca49037a101d9 /projects

4. Paste in Query

After creating your three parameters and selecting OK from the Manage Parameters window, the sidebar of your Power Query Editor should list the 2 variables you just set.

Once you have your parameters, select Query 1 from your sidebar, and then select Advanced Editor in the toolbar. Remove any existing query and paste the M Query into the editor. Press Done.

You should now see a list of records from your Braineet portfolio’s table.

Authentication

You may be asked to provide Authentication for https://URL.braineet.com. In this menu, you should select Basic for the Authentication mechanism.

The user name is the email of your account.
The password is your API key. You can generate one from your portfolio by clicking on Generate API key:

FAQ

How do I connect Braineet to Microsoft Excel Power Query?
The process here is very similar to the process in Power BI. To get started, open a new workbook, and then click on Data, Get Data, From Other Source, and Blank Query. The rest of the steps are the same as above.
How do I automate refreshes of my Power BI report?
To keep your data up to date, Power BI allows you to schedule a refresh of a report once it's been published in your organization’s workspace. Microsoft outlines how to set up a scheduled refresh in their support documentation.
Can I get historical data?
Yes, we advice you to pre-fetch multiple time the data with a code like this:

let
    CurrentDate = DateTimeZone.FixedLocalNow(),
    ListIntervalDates = List.DateTimeZones(DateTimeZone.From(CurrentDate),
     12,                            // 12 fetch every 30 days
     #duration( -30, 0, 0, 0 ) ),
    TableIntervalDates = Table.FromList(ListIntervalDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TablesWithDate = Table.TransformRows(TableIntervalDates,(row) as table =>  
        Table.AddColumn(Table.AddColumn(
            Table.FromList(Json.Document(Web.Contents(PLATFORM_URL, [RelativePath = "/api/rest/portfolio/" & PORTFOLIO_ID & "/projects?" & Uri.BuildQueryString([at = DateTimeZone.ToText(row[Column1], [Format="O", Culture="en-US"])])]))[data],
             Splitter.SplitByNothing(), null, null, ExtraValues.Error),
              "Report From", each if row[Column1] = CurrentDate  then "Latest" else DateTimeZone.ToText(row[Column1]), type text), "Last report update", each CurrentDate, type datetimezone
    )),
    RecordTable = TablesWithDate{0}[Column1],
    GetRecordFields = List.Distinct(List.Accumulate(RecordTable, {}, (s,c)=> s&Record.FieldNames(c))),
    CombinedTable = Table.Combine(TablesWithDate),
    CombinedTableExpanded = Table.ExpandRecordColumn(CombinedTable, "Column1",GetRecordFields, GetRecordFields)
in
    CombinedTableExpanded

This code will fetch 12 times the data with the query param at (every 30 days).

Have a column with a value list, how to display the data?
You just need to click on the right icon from the column and click on “Extract values”.
Then you can choose a delimiter if it’s a Pick List (Multi-Select) field or none: