Power BI not refreshing online when getting JSON data from kobotoolbox api

[Soabie_Mando]

Hi everyone,

I had problems with data refreshing online in a Power BI report in which I show a dashboard using data collected with kobo forms which the reprot is fetching using the kobotoolbox api in JSON format, the error messages vary, but mostly the error message is as follows:
“We cannot convert the value “[Record]” to type Record”
after a lot of testing, I found that the problem occurs with JSON format with the following steps:

• sort rows
• expand table rows (after merging the data with other queries)
• filter rows (when removing nulls from a key column (each row is unique))

after removing these steps the problem goes away…

I tried to change the authentication by going anonymous and using a token with the header option using web.contents as follows:
Headers = [#“Authorization” = "token " & “xxxxx”]
but it didn’t work

the weird thing is that it works just fine on Power BI desktop, but when I publish it and refresh it online I get the error message

in the end I was able to figure out the main cause of this problem, it was lying in the expanding of the record column of the imported table…
I was using a dynamic expanding and renaming by using lists from another imported table like this:
Table.ExpandRecordColumn(#“Expanded patients_data”, “patients_data”, oldNames, newNames)
but when I changed it to static names all the problems I was facing were solved

I hope that this problem gets a fix as dynamic renaming is somewhat more convenient

[Anonymous]

Hi @Soabie_Mando ,

-- “We cannot convert the value “[Record]” to type Record”
Please check the threads below. Please try to set the query that expands the source of the column name to the same privacy level as this query.

In PBI Service, the privacy level is under Gateway Connections.

Solved: Expression.Error: We cannot convert the value "[Re... - Microsoft Fabric Community
Solved: We cannot convert the value "[Record]" to type Lis... - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum