API call results in part-populated columns with "Record" in them. I need all available expanded.

[SevsBo]

We're bringing some data in via API, and it's a process and a half to get it in.

When data is ingested, it's first a list, and the list expands into a table:

That is then converted to a table:

That can be expanded into a full table, but this is where the problem starts:

All of those "Record" items can be expanded into two columns, but because there are gaps in the data, we cannot expand the whole column at once. There are no arrows at the column header to do so.

We know some of the data is blank, this is fine, but we need to see the expanded columns for every Record item. Is this possible?

[PhilipTreacy · Accepted solution]

Hi @SevsBo 

As suggested by @lbendlin  you can use Value.Is to check if the column contains a record and then extract the data, like so

if Value.Is([business_service], type record) then [business_service][ColA] else ""

Alternatively you can use the optional field selector '?' to do the same

= [business_service][ColA]?

In both cases you will need to create Custom Columns and then delete the original  business_service column.

Download example PBIX file here with both approaches

Regards

Phil

[lbendlin]

Value.Is   is your friend here.  You need to probe if [Business_value] is of type Record or of type null.  In the latter case you need to provide a dummy blank record that can be expanded just like the actual record.