Issue with ‘Scheduled Refresh’ for api calls

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! 

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.

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.

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