Running infinite refresh in Power BI service

[PowerBI1254]

Hello,

refresh is running in infinite loop.

What I am doing:

1. I got the product IDs from the database using Web.Content API call.

2. For each product ID from step 1, make an API call using Web.Contnet to get the product details.

# Function to get the product details is as below,

(pid) as table =>
let
Source = Table.FromColumns({Lines.FromBinary(
Web.Contents(
"http://test:12555/getaAllCropPestsForPowerBI",
[
Query = [
pid = Number.ToText(pid)
],
Timeout=#duration(0,0,0,60000)
]
), null, null, 65001)}),
#"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"result"}, {"Column1.result"}),
#"Expanded Column1.result" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.result", {"crops"}, {"Column1.result.crops"}),
#"Expanded Column1.result.crops" = Table.ExpandListColumn(#"Expanded Column1.result", "Column1.result.crops"),
#"Expanded Column1.result.crops1" = Table.ExpandRecordColumn(#"Expanded Column1.result.crops", "Column1.result.crops", {"languageCode", "countryCode", "cropId", "pestId", "lastUpdated"}, {"Column1.result.crops.languageCode", "Column1.result.crops.countryCode", "Column1.result.crops.cropId", "Column1.result.crops.pestId", "Column1.result.crops.lastUpdated"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.result.crops1",{{"Column1.result.crops.languageCode", "languageCode"}, {"Column1.result.crops.countryCode", "countryCode"}, {"Column1.result.crops.cropId", "cropId"}, {"Column1.result.crops.pestId", "pestId"}, {"Column1.result.crops.lastUpdated", "lastUpdated"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"cropId", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "cropId"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"cropId", Int64.Type}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"pestId", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "pestId"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"pestId", Int64.Type}})
in
#"Changed Type1"

# Populate Product details

let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents(
"http://test:12555/getaAllCropPestsForPowerBI",
[
Query = [
getPids = "true"
],
Timeout=#duration(0,0,0,60000)
]
), null, null, 65001)}),
#"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"result"}, {"result"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Expanded Column1", {{"result", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "result"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"result", Int64.Type}}),
#"Renamed Columns" = Table.Buffer(Table.RenameColumns(#"Changed Type",{{"result", "pid"}})),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "result", each fnGetAllCropPests([pid])),
#"Expanded result" = Table.ExpandTableColumn(#"Invoked Custom Function", "result", {"languageCode", "countryCode", "cropId", "pestId", "lastUpdated"}, {"result.languageCode", "result.countryCode", "result.cropId", "result.pestId", "result.lastUpdated"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded result",{{"pid", Int64.Type}, {"result.languageCode", type text}, {"result.countryCode", type text}, {"result.cropId", Int64.Type}, {"result.pestId", Int64.Type}, {"result.lastUpdated", type datetime}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"result.languageCode", "languageCode"}, {"result.countryCode", "countryCode"}, {"result.cropId", "cropId"}, {"result.pestId", "pestId"}, {"result.lastUpdated", "lastUpdated"}})
in
#"Renamed Columns1"

Problem Statement - 

Above code is working fine in Power BI desktop on my laptop, it is taking nearly 2 hrs to complete the full refresh but as soon as I publish this to power BI service it is running infinitely(starting from the first product once last product details are fetched).

I am expecting to complete the refresh as soon as it pull the product details for the last product.

so I am not  getting what mistakes I have done, I am new to Power BI and any help is Appreciated.

I am using Power BI Premium Per User.

[PowerBI1254]

Hello,

Yes correct I am monitoring the API side, 60000 timeout is getting ignored.

[lbendlin]

Check the refresh history for the semantic model, you should see up to three retries.

[PowerBI1254]

Appreciate your response. I observed that there's only one refresh entry, however, in the API logs, it indicates that the API call is being made for the first product after the completion of the last product from the previous refresh.

[lbendlin · Accepted solution]

That's probably the meta data call.

Chris Webb's BI Blog: Why Does Power BI Query My Data Source More Than Once? (crossjoin.co.uk)

[lbendlin]

as soon as I publish this to power BI service it is running infinitely(starting from the first product once last product details are fetched

how do you know this is happening?  Are you monitoring the API side?

Maximum timeout for semantic models is 18000 seconds.  so your 60000 value will be ignored.