Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 😊
Solved! Go to 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?
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!
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
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?