With the increased popularity of Microsoft Power BI in mind, we have made our API interface even easier to use.
14th September 2023
Salma Crisp MRICS
The Housing API allows for reporting, updating and ETL processes to be run on the data held within your Lifespan Housing account.
Power BI Desktop
The following is based on Power BI Desktop 2.112.603.0 64-bit (December 2022) running on Windows 10.
1. Start Power BI desktop and in the start-up screen, click on Get data
2. Select Other and then Web. Click Connect
3. Click on Advanced in the web details page and fill in the details as follows
· URL parts should be the endpoint of the API you are calling
· Type in X-API-Key into the HTTP request header parameters
· Type or paste in your personal key into the header value text box
It is important that all URLs are entered using HTTPS and that the key be provided as a request header and not included anywhere in the URL itself. This will ensure that the key is not transmitted in plain text and seen by a third party. Any attempts to call the API over HTTP will not succeed.
4. Click OK
The data should load in Power BI.
Excel
1. Go to the Data tab
2. Click on Get Data, then From Other Sources and then From Web
3. Click on Advanced in the web details page and fill in the details as follows
· URL parts should be the endpoint of the API you are calling
· Type in X-API-Key into the HTTP request header parameters
· Type in your personal key into the header value text box
4. Click OK
5. Leave access as Anonymous and click Connect
Using Web API is possible but requires manually setting the API Key name and will also result in the key being set as part of the URL, which is less secure. We already specify the key in the header, so leaving it as anonymous will work.
Excel will download the data from the API endpoint that was entered in step 3 and display it in the Power Quey Editor prior to loading into an Excel sheet. Here, we can modify and translate the data into a better format for us to use in Excel. In the steps above, we use the Property endpoint as the data we want to work with. This fetches all addresses stored within Housing, but you will probably find Excel will list them as just a series of Records.
To transform these records into something more useful before loading them into our worksheet, we can do so by
1. Right click on the List column header cell and choose To Table
2. Leave the table options as the defaults and click OK
3. The List column will now be converted to a table and be renamed to Column1. Click on the Expand Columns icon in the header cell
4. Select which columns you want to include/show and then click OK
5. The editor should now show all data within the columns that you selected.
6. When the data is in the format you need it to be, click on Close & Load. This will close the query editor and load the data into the current Excel Worksheet, displaying it as a formatted linked table
For a full break down on housing API please contact us