This document describes the procedures for using the Power Query Editor in Power BI to view and analyze eLynx data acquired via the eLynx API.  It describes a sample scenario for how to connect to the eLynx API and how to configure Power BI using a script and parameters.

NOTE:  These basic instructions also apply to using Power Query in Excel even though the example screenshots are from Power BI.  The script examples used in this document may be copied/pasted as a template but they must be modified to match your situation (e.g., your API key, report name, column configurations, etc.).

Prerequisites

The following items are needed to view and analyze eLynx data using Power BI:

1. API key for the eLynx Public API
2. Report that returns desired data
3. Basic understanding of how to use the Advanced Editor in Power BI to create Power Query queries

For more information about using Power BI and Power Query, please see the following article: https://docs.microsoft.com/en-us/power-query/power-query-quickstart-using-power-bi

Connecting to eLynx API with Power BI Desktop

STEP 1:  Create pivot function query

A pivot function must first be created to generate a data set that can be used in Power Query.  The steps to do this are as follows:

1. Open the Power BI Desktop application.
2. Click the Edit Queries button in the toolbar.  The Power Query Editor will be displayed.
3. Click the New Source button in the toolbar, and then choose the Blank Query menu option.  A new blank query will be created.
4. Click the Advanced Editor button in the toolbar.  The Advanced Editor window will be displayed.

1. Paste the following code into the Advanced Editor:

(row as list) => 
let
   rowTable = Table.FromList(row, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   expandedRecordTable = Table.ExpandRecordColumn(rowTable, "Column1", {"val"}, {"Column1.val"}),
   transposedTable = Table.Transpose(expandedRecordTable)
in
   transposedTable

1. Click the Done button to save the code in the Advanced Editor.
2. The newly created function should now be displayed in the Queries pane (left side of page).
3. IMPORTANT:  In the Queries pane (left side of page), rename the function that is created to "pivotData".

STEP 2:  Create parameters for the main query

Four parameters must be created for the pivot function:

• apiToken
• reportName
• startDate
• endDate

The steps to create these parameters are as follows:

1. In the Power Query Editor, click the Manage Parameters button to bring up the Parameters dialog.
2. Click the New link to add each of the four parameters.
   1. Set the Type value of each parameter to "Text".
   2. Place a checkmark in the Required checkbox for each of the parameters.
3. Click the OK button when finished to save the new parameters and close the Parameters dialog.
4. The newly created parameters should now be displayed in the Queries pane (left side of page).
5. Click the Save button (disk icon, upper left) to save the query.

STEP 3:  Create Main Query

The Main Query is created to retrieve data using the Pivot Function and parameters created above. The steps to do this are similar to those used to create the Pivot Function as follows:

1. Click the Edit Queries button in the toolbar.  The Power Query Editor will be displayed.
2. Click the New Source button in the toolbar, and then choose the Blank Query menu option.  A new blank query will be created.
3. Click the Advanced Editor button in the toolbar.  The Advanced Editor window will be displayed.
4. Paste the following code into the Advanced Editor:

let
   Source = Json.Document(Web.Contents("https://api.elynxtech.com/v1/datareport/" & reportName & "/?reportStartTime=" & startDate & "&reportEndTime=" & endDate, [Headers=[#"Ocp-Apim-Subscription-Key" = apiToken]])),
tables = Source[tables],
   topTable = Table.FromList(tables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   flattenedTopTable = Table.ExpandRecordColumn(topTable, "Column1", {"id", "tableName", "columns", "rows"}, {"Column1.id", "Column1.tableName", "Column1.columns", "Column1.rows"}),

   //get the columns
   colsOnlyTable = Table.RemoveColumns(flattenedTopTable,{"Column1.id", "Column1.tableName", "Column1.rows"}),
   colsRecordColumnTable = Table.ExpandListColumn(colsOnlyTable, "Column1.columns"),
   colNamesList = Table.Column(Table.ExpandRecordColumn(colsRecordColumnTable, "Column1.columns", {"caption"}, {"caption"}), "caption"),

   //get the rows
   rowsOnlyTable = Table.RemoveColumns(flattenedTopTable,{"Column1.id", "Column1.tableName", "Column1.columns"}),
   rowsRecordColumnTable = Table.ExpandListColumn(rowsOnlyTable, "Column1.rows"),
   cellsList = Table.Column(Table.ExpandRecordColumn(rowsRecordColumnTable, "Column1.rows", {"cells"}, {"cells"}), "cells"),
   recordsList = List.Transform(cellsList, each pivotData(_)),
   recordsListTable = Table.FromList(recordsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   firstRowTable = Table.FirstValue(recordsListTable),
   finalTable = Table.ExpandTableColumn(recordsListTable, "Column1", Table.ColumnNames(firstRowTable), colNamesList)
in
   finalTable

1. Click the Done button to save the code changes and close the Advanced Editor.
2. The newly created query should now be displayed in the Queries pane (left side of page).
3. Optionally, the new query can be renamed to whatever the user prefers (e.g., "TestQuery").

The Power Query Editor should now look something like this:

1. To finish, click the Close & Apply button in the toolbar to start using the data from the query in Power BI or Excel.

Power Query Credentials

Since the code that calls the eLynx API embeds the API Key in a header, the only type of Power Query credentials that will work is "Anonymous".  Any other credential type will cause an error stating that headers cannot be directly added when using that credential type.

The first time the query is run, you should be asked to set your credentials; at this point, select "Anonymous".

To set the credential type after the initial run, click Data source settings in the toolbar of the Power Query Editor.  In the dialog that comes up, click on the line for https://api.elynxtech.com/ then click the Edit Permissions… button.  If the Credentials type shown is not "Anonymous", as seen here, click the Edit… button and select "Anonymous".