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 getting the date comparator from other queries)
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
Hi @Sobaie_Mando,
Is it possible to share your PBIX file privately to review? because there might be lots of reasons and your explanation is not enough to understand the problem
1 Like
Hi @osmanburcu
thanks for the reply,
unfortunately the data belongs to the organization I work for. However I was able to have some progress…
turns out that when removing nulls from a key column (each row is unique) and that column was expanded from a record column this problem occures.
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.
I was able to do a workaround by filtering other columns, but still the query is very big and I’m still working on it…
I’ll keep you updated, and thank you for the response.
I figured 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
