Understanding the Data Available
Before connecting to our API, it may be useful for you to understand what Star data you will have available to you via the API. In essence, our Data Extract endpoints mimic the “Full Extract” and “Service user and Star export” spreadsheets that your Account Lead can download in OS Online.
Please be aware that all data coming from the basic licence endpoints is fully anonymised.
The “Service User and Star export” is a subset of the “Full Extract”. However, Star date is formatted differently in these two exports. In the “Full Extract”, the Star data is shown in the Star Entries sheet, with each row representing a single Star created. In the “Service User and Star export”, the Star Entries sheet includes each row as an engagement, with the Stars created included along that row.
We generally find that clients tend to find the “Full Extract” easier to work with, but take a look at both extracts and think about what is most suitable for your analysis.
To view and review the data available, your OS Online Account Lead can download the spreadsheets in OS Online Reporting section.
Integrating OS Online with PowerBI
- Create a blank query:
- Open the Advanced Editor:
- Insert code, altering it for your requirements:
let
url = “https://triangle.qes-online.com/APiSandboxApi/api/patients-extract/full-excel”, /*replace “full-excel” if you require an alternative endpoint*/
body =
“{
“”fileNameWithoutExtension””: “”fullextract””,
“”includeAccountDetails””: “”true””,
“”includeAssociatedUsers””: “”true””,
“”includeAssociatedServices””: “”true””,
“”includeLicences””: “”true””,
“”includeServiceUsers””: “”true””,
“”includeStarEntries””: “”true””,
“”includeEngagements””: “”true””,
“”includeActionPlans””: “”true””,
“”includeNotes””: “”true””
““showAnonymous”“: “”false””
}”, /*See API Endpoints to see what is required in the body for each endpoint. If using “”filenameWithoutExtension”” you can name the file as you please*/
Source =
Web.Contents(
url,
[Headers=
[#”Content-Type”=”application/json”,
Authorization=”Bearer KEY”], /*replace KEY with the API key which has been generated in SOL*/
Content=Text.ToBinary(body)])
in
Source
- Select the table you would like to work by clicking the relevant ‘List’ drill through button.
- Select Transform > Into Table with no delimiters.
- Expand the Records by selecting the box besides the column heading.
- Select Close & Apply