I have access to an API that lets me retrieve results of tests undertaken by my company employees. Each month, employees get one or more tests. The total set of tests all employees get in a month are grouped into a gropu called a PST, and identified by a unique ID named PstId. There are two API endpoinst related to my question here.
https://us.api.knowbe4.com/v1/phishing/security_tests
- In the returned data, there is a column named PstId, which is the above mentioned unique ID.
https://us.api.knowbe4.com/v1/phishing/security_tests/{PstId}/recipients?per_page={RecordsPerPage}&page={PageNumber}
- As you can see, there are 3 parameters that needs to be passed to the endpoint:
- PstId - The ID of the PST to retrieve results from
- RecordsPerPage - Essentially, how many records are returned per page. Min = 1 and Max = 100.
- PageNumber - The page number. Starts from 1.
I can easily call the 1st endpoing to get a list of PSTs, like so:
PSTs = Json.Document
(
Web.Contents
(
"https://us.api.knowbe4.com/v1/phishing/security_tests",
[
Headers=
[
ApiKeyName=#"APIKeyName",
Authorization="Bearer " & #"APIKeyValue"
]
]
)
)
Next, I can also retireve data from the 2nd endpoint using a function (the API doesn't return the total number of pages or total number of records, so I have to do this bit dynamically).
fnGetTestResults = (PstId as text, PerPage as number, PageNum as number) =>
let
Source = Json.Document
(
Web.Contents
(
"https://us.api.knowbe4.com",
[
RelativePath="/v1/phishing/security_tests/" & PstId & "/recipients",
Query=
[
per_page=Number.ToText(PerPage),
page=Number.ToText(PageNum)
],
Headers=
[
ApiKeyName=APIKeyName,
Authorization="Bearer " & APIKeyValue
]
]
)
)
in
Source
So far so good. However, my end goal is to retireve all data from all tests, and set up the PowerBI Service so that going forward, this will be auto refreshed. To do that, I created a 2nd function, called fnIteratePSTs. I pass the PstId to this a parameter, and from within it, I called the fnGetTestResults function.
fnIteratePSTs = (PstId as text) =>
let
Source = List.Generate(
()=> [ Offset = 1, Results = fnGetTestResults(PstId, 100, 1) ],
each not (List.IsEmpty([ Results ])),
each [ Results = fnGetTestResults(PstId, 100, [Offset] + 1 ), Offset = [Offset] + 1 ],
each [Results]
)
in
Source
Now, I call this function using the 'Invoke Custom Function' option from the PSTs table I created previously. This basically goes through all the PstIds, calls the fnIteratePSTs() function, which in tern iteratively calls the fnGetTestResults() function, all of which results in retireving results of all tests.
TestResults =
let
Source = PSTs,
#"Removed Other Columns" = Table.SelectColumns(Source,{"campaign_id", "pst_id", "name", "Campaign Start", "Status"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"pst_id", type text}, {"campaign_id", type text}, {"name", type text}, {"Status", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"campaign_id", "CampaignId"}, {"name", "Period Title"}, {"pst_id", "PstId"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"CampaignId", "Period Title", "PstId"}),
#"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "Results", each fnIteratePSTs([PstId])),
#"Expanded Results" = Table.ExpandListColumn(#"Invoked Custom Function", "Results")
in
#"Expanded Results"
This works fine, and I can get all the results.
However, when I publish this report to the PowerBI Server, and schedule a refresh (or even if I simply manually refresh from PBI service), it fails with the following message:
Data source error: [Unable to combine data] Section1/TestResults/Expanded Results references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: TestResults.
Cluster URI: WABI-WEST-US-E-PRIMARY-redirect.analysis.windows.net
Activity ID: c0ad9498-2a5d-403f-bf2a-eb03b4d8df7f
Request ID: f7b6cb6d-ac16-26ec-4ae8-631e237cfb11
Time: 2024-09-13 19:22:40Z
After some digging, I found out that it's the 'Expanded Results' step in the TestResults table that causes this issue. If I remove that step, and simply stop at the 'Invoke Custom Function', then PBI Service no longer reaises this issue. But without that step (and steps after that), the retirived data is useless, since expanded results column is what contains all the actual test results.
What is causing this and how do I fix it?
If this is not the right way to handle a situation like this, then what is?
