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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danxshap
Helper I
Helper I

New refresh error in dataflows with query that works fine in Power BI Desktop

Hi all,

 

All of a sudden on April 9th (last Friday) a few of my dataflows started failing to refresh every time even though they've been working for months, and I haven't changed the dataflows.

 

The error message in the refresh history is: Error: Expression.Error: We cannot apply field access to the type Null

 

I think I've got it narrowed down to this call to List.Generate:

 

 

 

// Generate a list of records with keys "PageJson" (results from Sell API), "IncludeNextRecord", and "PageNumber"
// But then have the resulting list be from selecting the "items" key from "PageJson" for each page
// So we'll have a list of lists
SellResults = List.Generate(
    () => [PageJson=GetSellPageJson("1"), IncludeNextRecord=true, PageNumber=1],
    each [IncludeNextRecord] and ([PageNumber] <= MaxPages or MaxPages = null),
    each [
        IncludeNextRecord = Record.HasFields([PageJson][meta][links], "next_page"),
        PageJson = if Record.HasFields([PageJson][meta][links], "next_page") then GetSellPageJson(Number.ToText([PageNumber] + 1)) else null,
        PageNumber = [PageNumber] + 1
    ],
    each [PageJson][items]
),

 

 

 

Because if I replace else null with else "N/A", then the error message changes to: Error: Expression.Error: We cannot apply field access to the type Text

 

If I create a query in Power BI Desktop with the exact same code, it will refresh fine — I'm only getting these errors in dataflows and they only spontaneously started happening on every refresh attempt starting on April 9th and after.

 

If someone could please help me understand what might be the cause of this problem and how I can best work around it, I'd appreciate it very much 😊

1 ACCEPTED SOLUTION

Hi Karlos, thanks for the reply!

 

I'm not using RLS.

 

I have an update which is that I changed the follow line:

 

PageJson = if Record.HasFields([PageJson][meta][links], "next_page") then GetSellPageJson(Number.ToText([PageNumber] + 1)) else null

 

To instead be:

 

PageJson = if Record.HasFields([PageJson][meta][links], "next_page") then GetSellPageJson(Number.ToText([PageNumber] + 1)) else []

 

And when that happened, I got a new error: Error: Expression.Error: The field 'meta' of the record wasn't found

 

Once I saw that, I changed this line:

 

IncludeNextRecord = Record.HasFields([PageJson][meta][links], "next_page")

 

To instead be:

 

IncludeNextRecord = Record.HasFields([PageJson], "meta") and Record.HasFields([PageJson][meta][links], "next_page")

 

And the dataflow successfully refreshed 🎉

 

I'm totally stumped as to what might have changed on/around April 9th that introduced this problem, and why these changes aren't required on Power BI Desktop in order to get the query to run without errors.

 

Also worth noting: I saw this same problem in a similar List.Generate call in a different dataflow that is querying data from a different API, so it seems like a sudden change in the data being returned from the API is not the culprit.

 

Anyone have any thoughts on how to explain this behavior?

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @danxshap  ,

You could read the following article ,it tells about The Environment concept in M for Power Query and Power BI Desktop.Wish it is helpful for you!

The Environment concept in M for Power Query and Power BI Desktop

 

Also you can refer to the Power Query Language Specification:

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

Anonymous
Not applicable

Are you using RLS on this column to share these dashboards? If so, it might be that you need to select who can see the null / N/A data. 

If not that, I think the column might be expecting a specific data type (data or number) but instead it's getting null which is being treated as a text value. It would be worth changing the "null" to a "0" or "01/01/2000" to match the column type to see if the error continues. 

Hi Karlos, thanks for the reply!

 

I'm not using RLS.

 

I have an update which is that I changed the follow line:

 

PageJson = if Record.HasFields([PageJson][meta][links], "next_page") then GetSellPageJson(Number.ToText([PageNumber] + 1)) else null

 

To instead be:

 

PageJson = if Record.HasFields([PageJson][meta][links], "next_page") then GetSellPageJson(Number.ToText([PageNumber] + 1)) else []

 

And when that happened, I got a new error: Error: Expression.Error: The field 'meta' of the record wasn't found

 

Once I saw that, I changed this line:

 

IncludeNextRecord = Record.HasFields([PageJson][meta][links], "next_page")

 

To instead be:

 

IncludeNextRecord = Record.HasFields([PageJson], "meta") and Record.HasFields([PageJson][meta][links], "next_page")

 

And the dataflow successfully refreshed 🎉

 

I'm totally stumped as to what might have changed on/around April 9th that introduced this problem, and why these changes aren't required on Power BI Desktop in order to get the query to run without errors.

 

Also worth noting: I saw this same problem in a similar List.Generate call in a different dataflow that is querying data from a different API, so it seems like a sudden change in the data being returned from the API is not the culprit.

 

Anyone have any thoughts on how to explain this behavior?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors