Json expansion - no columns were found

[Macc-PA]

Hi,

I am reading a table containing a json column using OData.

The column in the database is defined as nvarchar(max).

The json looks fine in the table and I can transform the column from Text to Json.

When I try and expand the column, I get 'No columns were found' in the popup.

When I click on the record, I see the correct fields so the data is there.

However if I read the same table directly from SQL Server into Power BI the expansion works fine.

For security reasons we have to use Odata.  Can anyone help with what the problem is when using Odata?

Thanks

[Macc-PA]

Hi,

Thanks for getting back.  I have added the custom column. There are no empty rows and the count retruns 7 fields.

If I click on any of the records it shows the 7 individaul fields.

What else can I try?

[ppm1 · Accepted solution]

When you go to expand that Record column and see "No columns ...", click on "Load more" in bottom right. I was able to reproduce your issue and this worked.

Pat

[ppm1]

I suspect the Record on your first row is empty, which is what is used to get the field names to expand. There are several ways to solve that. One is to add a custom column with the count of field names with the expression below and then sort by that column descending (and then expand it, and it should work).

= Record.FieldCount([Custom]) //replace Custom with your column name

Pat