Forum Discussion
Issue with ‘Scheduled Refresh’ for api calls
Hi Anonymous,
It seems like the power query not allow you direct do merge operations between different types(number and text), please try to use the following code of 'add custom column' step, I added text type convert in the formula.
#"Added Custom" = Table.AddColumn(#"Converted to Table1", "Source", each Json.Document(Web.Contents("https://api.xyz.com/", [ Headers=[#"Gen-Api-Key"="...."], RelativePath="natgas/events/v1/notices/details", Query= [ ids=Text.From([Column1]), format="json" ] ])))Regards,
Xiaoxin Sheng
Hi Anonymous and all: Good day! I found checking the data in the data base table and Api portal that for some pipes there will be same event posted for different locations [as they are impacted for all those locations for a pipeline]. That's why the multiple location rows are coming for those pipes! So, it's actually not an error/issue, but a fact! So, I am closing this thread. Thanks all for your patience. 🙂
Hi Anonymous,
>>Now, I am not sure how to update code for the other api, ‘Notice details’.
You can extract the id list from the 'query 1' to a new query table and convert them to the table.
Then you can add a custom column to the new table to use the 'notice detail' function with id field values to looping each row to get detailed records instead of using query parameters to manually invoke.
Regards,
Xiaoxin Sheng
Hi Anonymous: Thanks for your response. I am not sure I can follow you clearly! I am quite new to Power BI world. Could you please show the code to do it, would really appreciate it a lot.
Hi Anonymous,
>>You can extract the id list from the 'query 1' to a new query table and convert them to the table.
Select one column and right click on it to choose 'Add as new query', then this column values will be extracted as a new 'query' with 'list' format.
Right-click on the new query list created by the above step, choose 'to table' to convert it to table format.
>>add a custom column to the new table to use the 'notice detail' function with id field values to looping each row to get detailed records
Add a new column to the current table and use the connector function to invoke data based on current id: (use id column name as a parameter in the connector)
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Source", each Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & [NoticeId] & "&format=json", [Headers=[#"Gen-Api-Key"="……"]])))Notice: [NoticeId] is the id column name, you can replace it with your field name.
Power query operators#list-of-m-operators
Regards,
Xiaoxin Sheng
Hi Anonymous and all:Thanks a lot for your response. I tried to follow the steps you mentioned. I am not sure what to do at the last step you mentioned:
Add a new column to the current table and use the connector function to invoke data based on current id: (use id column name as a parameter in the connector)
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Source", each Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & [NoticeId] & "&format=json", [Headers=[#"Gen-Api-Key"="……"]])))
I selected ‘Add Column’-> ‘Custom Column’-> then copy-pasted the code in ‘notice_detail’ function in the ‘Custom column formula’.
After that I get a new Column, ‘Custom’ with ‘Function’ in each cell for different ids in the table. Not sure what to do now?
The code from advanced editor [in blue is similar to your code]
-----------------------------------
let
Source = Json.Document(Web.Contents("https://api.xyz.com/",
[
RelativePath="natgas/events/v1/notices",
Query=
[
priorities="critical",
limit="50",
offset="0",
format="json"
]
,
Headers=[
#"Gen-Api-Key"="......."
]
]
)),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "pipelineName", "priority", "type", "subject", "postDate", "effDate", "endDate"}, {"id", "pipelineName", "priority", "type", "subject", "postDate", "effDate", "endDate"}),
id1 = #"Expanded Column1"[id],
#"Converted to Table1" = Table.FromList(id1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table1", "Custom", each (NoticeId as number) => let
Source = Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & Number.ToText(NoticeId) & "&format=json", [Headers=[#"Gen-Api-Key"="......"]])),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "pipelineName", "pipelineId", "priorNoticeIdentifierId", "noticeIdentifierId", "priority", "type", "status", "subject", "body", "postDate", "effDate", "endDate", "insertDate"}, {"id", "pipelineName", "pipelineId", "priorNoticeIdentifierId", "noticeIdentifierId", "priority", "type", "status", "subject", "body", "postDate", "effDate", "endDate", "insertDate"})
in
#"Expanded Value1")
in
#"Added Custom"
---------------------------------------------------
Would really appreciate any help on this. Best, Sohana