Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PowerBI1254
Frequent Visitor

Running infinite refresh in Power BI service

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.

1 ACCEPTED SOLUTION
5 REPLIES 5
PowerBI1254
Frequent Visitor

Hello,

 

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

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

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
Super User
Super User

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors