Forum Discussion
Assistance with Power Query – Missing ‘Closed’ Status in Data Extract
Hi,
I need some assistance with Power Query. I’m using a script to retrieve all status data, but I’m not getting the expected results—for example, statuses marked as ‘Closed’ are missing. I can’t find any of these in the data extract.
let
// Define credentials
apiKey = "XXXX",
username = "XXXX",
password = "XXXX",
credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
authHeader = "Basic " & credentials,
// Pagination settings
pageSize = 100,
baseUrl = "https://api.XXXX.com",
relativePath = "v3/inf/records",
// Function to fetch a single page
GetPage = (offset as number) =>
let
queryParams = [
sort = "creationDate",
asc = "false",
mobileOnly = "false",
filter = "moduleName:Incident Reporting",
filter1 = "form:2",
filter2 = "archivedStep:true",
filter3 = "deleted:false",
rowStart = Text.From(offset),
rowLimit = Text.From(pageSize)
],
response = Json.Document(Web.Contents(baseUrl, [
RelativePath = relativePath,
Query = queryParams,
Headers = [
#"x-api-key" = apiKey,
#"Authorization" = authHeader,
#"Accept" = "application/json"
]
])),
results = response[results]
in
results,
// Generate pages dynamically until empty
allPages = List.Generate(
() => [offset = 0, data = GetPage(0)],
each List.Count([data]) > 0,
each [offset = [offset] + pageSize, data = GetPage([offset])],
each [data]
),
combined = List.Combine(allPages),
table = Table.FromList(combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = table,
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "complete", "creationDate", "invitations", "displayText", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}, {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "complete", "creationDate", "invitations", "displayText", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}),
#"Expanded fieldsToValues" = Table.ExpandRecordColumn(#"Expanded Column2", "fieldsToValues", {"Date Occurred", "docNumber", "status", "Motor Vehicle Incident Type"}, {"Date Occurred", "docNumber", "status", "Motor Vehicle Incident Type"})
in
#"Expanded fieldsToValues"
The third party is using this API link and is getting the expected results.
Hi PhilipTreacy
I found the correct one, I was missing {}filter = {"moduleName:Incident Reporting", "archivedStep:true", "form:2"}Thanks for all your guidance
12 Replies
Hi ashmitp869
The 3rd party API call you've shown is using a URL parameter filter=fieldsToValues.status:Closed but your M code is not. Do you need to include this in your query?
Have you tried calling GetPage in a separate query and confirming what data it returns?
Is it possible that in your query above, when you do the #"Expanded Column2" step that you aren't actually expanding the record with the Closed status data?
Regards
Phil
ashmitp869 I don't see anything obvious in your code that would exclude that status. Is it possible that one of your filters is inadvertently excluding closed items?
asc = "false", mobileOnly = "false", filter = "moduleName:Incident Reporting", filter1 = "form:2", filter2 = "archivedStep:true", filter3 = "deleted:false",Hi,
Can you help me with this
the actual path is like
actualPath = "https://api.XXX.com/v3/inf/records?rowLimit=100&sort=creationDate&asc=false&mobileOnly=false&filter=moduleName:Incident Reporting&filter=form:2&filter=archivedStep:true"
How to apply the filter with & in this Power M query -// Function to fetch a single page GetPage = (offset as number) => let queryParams = [ sort = "creationDate", asc = "false", mobileOnly = "false", filter = "moduleName:Incident Reporting", filter1 = "form:2", filter2 = "archivedStep:true", filter3 = "deleted:false", rowStart = Text.From(offset), rowLimit = Text.From(pageSize) ],I am trying to use this but not working
queryParams = [ sort = "creationDate", asc = "false", mobileOnly = "false", filter = "moduleName:Incident Reporting AND form:2 AND archivedStep:true", rowStart = Text.From(offset), rowLimit = Text.From(pageSize) ],PhilipTreacy GeraldGEmerick Please kindly assist.
HI ashmitp869
Are you sure that is the correct query parameter convention? Is that what the API documentation says?
It is unusual to have multiple parameters with the same name (filter). Certainly if you are calling a PHP script then this is bad and won't work as only the last filter parameter value is saved. But it will dependon what is receiving the request and how it is processed.
I would expect to use filter1, filter2 etc instead
If that really is the convention for this API try this
queryParams = [ sort = "creationDate", asc = "false", mobileOnly = "false", filter = "moduleName:Incident Reporting&filter=form:2D&filter=archivedStep:true", rowStart = Text.From(offset), rowLimit = Text.From(pageSize) ]Have you tried using postman to confirm the query format and the returned data?
Download Postman | Get Started for Free
Phil
I have tried Insomnia
I am getting the result.
I have usedetPage = (offset as number) => let response = Json.Document(Web.Contents( "https://api.myosh.com", // Static base URL [ RelativePath = "v3/inf/records", Query = [ rowLimit = "1000", sort = "creationDate", asc = "false", mobileOnly = "false", // filter = "moduleName:Incident Reporting", filter ="moduleName:Incident Reporting&filter=form:2&archivedStep:true", //filter2 = "form:2", //filter3 = "archivedStep:true", // filter4 = "deleted:false", offset = Number.ToText(offset) ], Headers = [ #"x-api-key" = apiKey, #"Authorization" = authHeader, #"Accept" = "application/json" ] ] )), results = try response[results] otherwise {} in results, but not getting the result as expected.
I have tried to use direct Url, it is extracting all data in PBI desktop.
When publish to PBI server - I am getting error"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. "
baseUrl = "https://api.myosh.com/v3/inf/records?rowLimit=1000&sort=creationDate&asc=false&mobileOnly=false&filter=moduleName:Incident Reporting&filter=form:2&filter=archivedStep:true&filter=deleted:false", // Function to get a page GetPage = (offset as number) => let url = baseUrl & "&offset=" & Number.ToText(offset), response = Json.Document(Web.Contents(url, [Headers = [ #"x-api-key" = apiKey, #"Authorization" = authHeader, #"Accept" = "application/json" ]] )), results = try response[results] otherwise {} in resultsPBI Service requires that the data source (your baseUrl) be static - that is, PBI Service doesn't like the query parameters in the URL.
Try this
baseUrl = "https://api.myosh.com/v3/inf/records", GetPage = (offset as number) => let response = Json.Document( Web.Contents( baseUrl, [ RelativePath = "", Query = [ rowLimit = "1000&sort=creationDate&asc=false&mobileOnly=false&filter=moduleName:Incident Reporting&filter=form:2&filter=archivedStep:true&filter=deleted:false", offset = Number.ToText(offset) ], Headers = [ #"x-api-key" = apiKey, #"Authorization" = authHeader, #"Accept" = "application/json" ] ] ) ), results = try response[results] otherwise {} in resultsRegards
Phil
Hi ashmitp869 ,
Thank you for reaching out to the Microsoft Community Forum.
You are not getting any “Closed” records because your Power Query request is not applying the same filter that the third-party API call is using. The third-party request shows below filter.
filter=fieldsToValues.status:Closed
But your Power Query script never includes this filter.
Please update your query parameters section with below code.
queryParams = [
sort = "creationDate",
asc = "false",
mobileOnly = "false",
filter = "moduleName:Incident Reporting",
filter1 = "form:2",
filter2 = "archivedStep:true",
filter3 = "deleted:false",
filter4 = "fieldsToValues.status:Closed",
rowStart = Text.From(offset),
rowLimit = Text.From(pageSize)
],I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
