Issue with ‘Scheduled Refresh’ for api calls

[Anonymous]

Hi all: Good day. I have developed a PBI dashboard that have 3 api calls [2 of them get called dynamically passing ‘Id’] out of several data sources . It works in Desktop env and with manual data refresh in Service env. But issues happen as Publish team is trying to implement ‘scheduled refresh’. The team told me I need to change the code for the api connection to make it happen!

#1 ‘Notice’ api url:

https://api.xyz.com/natgas/events/v1/notices?priorities=critical&limit=50&offset=0&format=json

There is a max limit of 50 notices I can pull. I changed the code in Advanced Editor as shown below:

----------------------------------------

let GetData = () =>

    let

        pageSize = 50,

        sendRequest = (page)

            let

                Response = Web.Contents(

                    "https://api.xyz.com/natgas/events/v1/notices",

                  [

                      Query = [

                            priorities=”critical”,

limit= Number.ToText(pageSize),                          

                            format="json",

                            offset= Number.ToText(page * pageSize)

                        ]

                  ,

                      Headers = [

                          #"Gen-Api-Key"= "………." [not showing the key here]

                          ]

                  ]

                ),

                Data = Json.Document(Response)[data]

            in

                Data,

        Loop = (page as number, AccumData as list) =>

            let

                Data = Function.InvokeAfter(()=>sendRequest(page), #duration(0,0,0,2)),        

                Result =     

                    if List.Count(Data) < pageSize

                    or page >= 50

                    then Table.FromRecords(List.Combine({AccumData, Data}))

                    else @Loop(page + 1, List.Combine({AccumData, Data}))

            in

                Result

        in

            Loop(0, {})

in

    GetData

-----------------------------

 It works to pull the data and show up in a table visual as expected!

Now, I am not sure how to update code for the other api, ‘Notice details’.

I  get 50 notices [‘Id’ is the unique key] from api#1, then I need to get notice details for each notice. I call #2 ‘notice details’ api sending the ‘Id’ [dynamically created]

#2 ‘Notice details’ api url->

https://api.xyz.com/natgas/events/v1/notices/details?ids={ids}&format=json

-I created a parameter NoticeId and

-then a query, ‘query_notice_details’ using New source-> Web-> Advanced-> steps using ‘NoticeId’ parameter

-I then created a Function on the query->NoticeDetailsFunction

-I copied #1 data source and removed columns except ‘Id’, invoked the above function to get  ‘details_notices’ data source

‘query_notice_details’: code From advanced editor:

let

    Source = Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & NoticeId & "&format=json", [Headers=[#"Gen-Api-Key"="……"]]))

in

    Source

-----------------------------------------------------------------

‘details_notices’: code

------------------------------------

let

    Source = notices_api(),

    #"Removed Columns" = Table.RemoveColumns(Source,{"pipelineName", "pipelineId", "priority", "type", "status", "subject", "postDate", "effDate", "endDate", "insertDate"}),

    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "NoticeDetailsFunction", each NoticeDetailsFunction([id])),

    #"Expanded NoticeDetailsFunction" = Table.ExpandRecordColumn(#"Invoked Custom Function", "NoticeDetailsFunction", {"data"}, {"data"}),

    #"Expanded data" = Table.ExpandListColumn(#"Expanded NoticeDetailsFunction", "data"),

    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "priority", "type", "subject", "body", "postDate", "effDate", "endDate"}, {"pipelineName", "priority", "type", "subject", "body", "postDate", "effDate", "endDate"})

in

    #"Expanded data1"

-----------------------------------------------

Not sure of the code change to make for invoking the function, NoticeDetailsFunction and iterate through for each NoticeDetailsFunction([id])?

---------------------------------------------------------------------------

I have already checked the link: https://eu001-sp.shell.com/sites/AAFAA3947/SitePages/SOURCES%20INFO%20-%20REST%20API.aspx

Sorry if I have overwhelmed you all the info! ☹ Many thanks in advance for your patience. 

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous  : Also as I tried to change [deleted few steps & updated the expressions] the code in my previous query I posted today  [to make it similar to your code snippet] as below 

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", "Source", each Json.Document(Web.Contents("https://api.xyz.com/natgas/events/v1/notices/details?ids=" & [NoticeId] & "&format=json", [Headers=[#"Gen-Api-Key"="........."]])))

in

    #"Added Custom"

---------------------------------------------------

I get Errors as below:

Any help would highly be appreciated. 

-----------------------------------------------------------------

[Anonymous]

Hi @Anonymous,

What error that displayed in the custom column who invokes the web connector? Can you please share the detailed error messages?

After I checked your formula and snapshot, I found your id field values are stored in 'column1', so you need to use 'column1' to replace noticed. (you can also try to add 'relativepath' and other optional parameters into the web connector):

	#"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=[Column1],
			format="json"
		]
	])))

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous and all : Good day! Thanks for your continued support. I got the following error:

After I replace NoticeId with Column1, get the following error:

So, I started again and didn’t delete any steps. I renamed “Column1” as “NoticeId”, edited the #“Added Custom” part as below with Relative path and Query, created a variable, id to use in ids=[id]:

-------------------------

let

    Source = Json.Document(Web.Contents("https://api.genscape.com/",

    [

        RelativePath="natgas/events/v1/notices",

        Query=

        [

            priorities="critical",

            limit="50",

            offset="0",

            format="json"

        ]

        ,

        Headers=[

            #"Gen-Api-Key"="f94ffe41c1d74066b90c564397edf9b2"

            ]

        ]

        )),

    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.genscape.com/",

        [

            Headers=[#"Gen-Api-Key"="f94ffe41c1d74066b90c564397edf9b2"],

            RelativePath=" natgas/events/v1/notices/details",

            #"id"= Number.ToText(NoticeId),

            Query=

            [

                 ids=[id],

                 format="json"

            ]

        ])),

        #"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"

--------------------------

Now I get the following with ‘function’ in each cell under ‘Custom’ Column

Not sure what to do?! Many thanks in advance. 

[Anonymous · Accepted solution]

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

[Anonymous]

Hi @Anonymous: Thanks a lot for your help. It removed the error & I can now pull the desired data. I will check with the Publish team to find out if this code change will resolve the issue/error we have been getting so far reg 'Scheduled Refresh' with dynamic query! 

[Anonymous]

Hi @Anonymous : The M code change helped the 'Scheduled Refresh' to be activated! Thanks a lot for your support through this process! 🙂

[Anonymous]

Hi @Anonymous: Good day. Though the Publish team is not getting the error before, I see an issue in the data. I get a Column, 'locations' as a List,  which earlier [before the M code change]- I could see as : delimited with location id & location name. I could then select the column, right click and select 'Split by delimiter'. I just posted the 'split by delimiter' option not showing up in

https://community.powerbi.com/t5/Desktop/Split-a-Single-Column-with-delimiter-colon/td-p/1428948

If I extract as new rows/ expand-> it doesn't work: rather creates multiple rows with same 'id'  and shows data  incorrectly in a Map visual.

I am thinking is it something more I have to do in the M code to split the list by colon [:] delimiter?

The code for this data source, #2 details_maintenance_events is as below [changing some value with xyz/abc/...]:

---------------

let
    Source = Sql.Database("xyz\Fundamentals", "NatGas_V3", [Query="select * from NatGas_V3.natgas.pipeline_maintenance_events#(lf)where endDate >=" &datefilter]),
    #"Sorted Rows" = Table.Sort(Source,{{"endDate", Order.Ascending}, {"updateDate", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each true),
    id1 = #"Filtered Rows"[id],
    #"Converted to Table" = Table.FromList(id1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Source", each Json.Document(Web.Contents("https://api.abc.com/",
    [
        Headers=[#"Gen-Api-Key"="........"],
        RelativePath="natgas/events/v1/maintenance-events/details",
        Query=
        [
            ids=Text.From([Column1]),
            format="json"
        ]
    ]))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Column1"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Column1"}),
    #"Removed Errors2" = Table.RemoveRowsWithErrors(#"Removed Errors1", {"Source"}),
    #"Expanded Source" = Table.ExpandRecordColumn(#"Removed Errors2", "Source", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Source", "data")
in
    #"Expanded data"

---------------------

First I pull data from a sql database as #1 maintenance_events data source, then I create this 2nd data source, details_maintenance_events. I haven't changed any code in #1 data source[ use get data-> sql database->....], which is as below:

----

let
    Source = Sql.Database("xyz\Fundamentals", "NatGas_V3", [Query="select * from NatGas_V3.natgas.pipeline_maintenance_events#(lf)where endDate >=" &datefilter]),
    #"Sorted Rows" = Table.Sort(Source,{{"endDate", Order.Ascending}, {"updateDate", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each true)
in
    #"Filtered Rows"

--------

Many thanks in advance.

[Anonymous]

Hi @Anonymous and all: Good day! I did some code change and posted this in another thread:

https://community.powerbi.com/t5/Desktop/Split-a-Single-Column-with-delimiter-colon/m-p/1430399

Please check my last post in that thread as that's where I am now! Many thanks in advance.

[Anonymous · Accepted solution]

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. 🙂