Search code examples
powerbipowerquerypowerbi-desktopm

ColumnsofType Record not returning any columns

I've got a table full of different data types, including records, that I want to extract all column names of records to then use in an expand function. I've included a screenshot of a column containing record's however,enter image description here when I use this = Table.ColumnsOfType(#"Expanded fields", {type record}), it returns an empty list .

enter image description here

I've tried looking through the entire column to see if there was anything different but its all record types. Any help please.

EDIT: enter image description here

Error using Table.TransformColumnTypes

Solution

  • Record is not a valid type to search for. And judging by your image, your type is Type.Any as denoted by the ABC123

    You best bet is to unpivot all the columns (perhaps those starting with a certain prefix) then on the new Value column, expand like so

    #"PriorStepNameHere" = .... , 
ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column(#"PriorStepNameHere", "Value"), each if _ is record then Record.FieldNames(_) else {}))),
Expand= Table.ExpandRecordColumn(#"PriorStepNameHere", "Value", ExpandList,ExpandList)