Connecting Excel to the CS Lucas data repository to retrieve data sets for building dashboards. This data can be combined with other data sets and other data sets outside the CS Lucas. The purpose is to deliver comprehensive insights that support critical decision-making in real-time.
These instructions detail creating an Excel template for retrieving datasets from the CS Lucas repository. The template may be shared with others with the necessary credentials so that they may retrieve the data, render it, and make further enhancements.
Pre-requisite
To access the data repository, you need the following:
| User ID: | A user ID of an account to access the repository. This account must be activated for OTP (MFA). See MFA activation: https://www.cslucas.com/user-guide/multi-factor-authentication/ |
| Token: | A 275-character text used for authentication, valid for 4 hours but token expiry can be configured. See Instructions: Get API Token. |
| Dataset: | The name of the required dataset from the repository. An inventory of all repositories and a sample dataset can be found here: Data Repository. |
| URL | A URL used to connect to the repository API. This URL contains all the above information. Construct this using Get API Token. |
| Power BI | You need to install Power BI Desktop. This guide is based on Version 2.138.782.0 |
The instructions use place markers and sample values for illustrative purposes. Replace them as appropriate.
| Placeholder | Values |
|---|---|
| Domain | Production: api2p-live.cslucas.com/prod UAT: api2p-live.cslucas.com/uat |
| Userid | cslpowerbiuser |
| OTP_Token | eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJDUyBMdWNhcyIsImp0aSI6IjVhNGIwNzQ1LTU3MGYtNDVjZS05MjVhLTYwYzViMmVkYTAyNCIsImlhdCI6MTc0MjgxMDg0Niwic3ViIjoiYTlmMGE1YzAtY2Y4NS00N2NjLTk0NzQtZWNlZTdlNWEyZDI1IiwiZXhwIjoxNzQ0MDIwNDQ2fQ.oTf2M5ZABbKO5XerHJUPBpyW_gN7K4l6KYlNptUrMno |
| As_at_date | Enter the date in this format: YYYY-MM-DD |
| Others | None |
| Dataset | ds1426 |
| URL | https://api2p-live.cslucas.com/dataset_name/uat?userid=cslpowerbiuser&otp_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJDUyBMdWNhcyIsImp0aSI6IjVhNGIwNzQ1LTU3MGYtNDVjZS05MjVhLTYwYzViMmVkYTAyNCIsImlhdCI6MTc0MjgxMDg0Niwic3ViIjoiYTlmMGE1YzAtY2Y4NS00N2NjLTk0NzQtZWNlZTdlNWEyZDI1IiwiZXhwIjoxNzQ0MDIwNDQ2fQ.oTf2M5ZABbKO5XerHJUPBpyW_gN7K4l6KYlNptUrMno&as_at_date=now&others=none |
| Source | = Json.Document(Web.Contents(Text.Combine({"https://",Domain,"/ds1426?userid=",Userid,"&otp_token=",Text.From(OTP_Token),"&as_at_date=",Text.From(As_at_date),"&others=",Text.From(Others),""}))) |
Connecting Excel
Step 1: Create the parameters
– Open Excel
– Select Data menu > Get Data > Launch Power Query Editor
– On the Home Tab, click on Manage Parameters
– Click on New
– Enter the following values to create Domain parameter.
Name: Domain (Case sensitive. Ensure the parameter name matches the <Source> in Step 4 below.)
Description: [blank]
Required: Checked
Type: Text
Suggested Value: Any values
Current value: <Domain>
– Click New to create the User ID parameter using the current value used in the <User ID>
– Repeat creation for the OTP_Token parameter.
Mark Required as Uncheck
Set current value = <Token>
– Repeat creation for the As_at_date parameter.
Mark Required as Uncheck
Set current value = <As_at_date>. Enter the date in this format: YYYY-MM-DD. E.g. 2017-07-02
– Repeat creation for the Others parameter.
Mark Required as Uncheck
Set current value = None
Step 2 – Connect to the Repository
– Click on New Source (ribbon)
– Select Other Source > Web
– In the From Web dialogue, paste the <URL> in the URL field.
– Click OK. In a moment, the data will be populated in a single column.
Step 3 – Transform the data
– Select the “Transform” ribbon above the Menu bar.
– On the ribbon, click on To Table Convert. In the pop up,
Select or Enter delimiter: None
How to handle extra column: Show as errors
Click OK
– Click on the Expand icon to the right edge of the grid header
– Select on Extract value. In the pop-up
For the delimiter to use for concatenating list values, select Tab
Click OK
– In the Home ribbon, select Split Column > By Delimiter
Select or Enter delimiter: Tab
Click OK
The data now resemble a table.
– In the Home ribbon, select Use First Row as Headers.
Step 4: Update Source to consume the parameters
– Select the View tab
– In the ribbon, select Query Settings. The Query setting panel appears on the right.
– Change the name to the name of the dataset. E.g. ds1426
– Under Applied steps, click on Source
– Expand the formula bar if necessary with the down arrow (v) to the right
– Select the entire content of the formula bar and replace it with <Source>. Include the “=” sign. Change the dataset name (illustrated here as ds1426) as appropriate.
– Save by clicking on the left check box.
– Select the Home menu, and in the ribbon, select Close and Load. The data loaded will be on a spreadsheet.
– Save the spreadsheet
Step 5: Apply a new OTP and Token
– Select the Data menu
– Select Get Data > Launch Power Query Editor
– To change OTP, select the relevant items on the left panel
– Enter the latest to replace the current value.
– To change the Token, select the relevant items on the left panel
– Paste the new Token to replace the current value.
– Select the Home menu, and in the ribbon
– Click on Refresh preview
– On the left panel, click on the name of the dataset. You should see the preview data
– select Close and Load
– Click on Refresh All
– The spreadsheet will be populated with the refreshed data.
You may now save the spreadsheet.
0 Comments