cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Extract Record Field Names to List

I'm deep into writing some functions to automate processing some fairly deep and complex JSON files. Since it is JSON coming from user generated data the number and names of the key:value pairs is indeterminate and can vary. In order to write a series of generic functions to expand and flatten the data I need to be able to create Lists of Record Names in order to expand tables and columns.

 

Where I'm stuck; I have:

 

  • A column that has Record Values in each row.
  • I want to extract the Field Names from each Row and build a List of all Record Names. I will then use that list in a function to expand the record column dynamically.

I'm trying to use Record.FieldName to extract the data, so far I have this:

 

(Tbl as table, ColumnName as text) =>

let
    #"KeepColumns" = Table.SelectColumns(Tbl, ColumnName), //reduce table to only column of interest
    #"GetRecordFields" = Table.AddColumn(#"KeepColumns", "RecordFields", each Record.FieldNames(ColumnName)) //get Record Field names as list
    
in
    #"GetRecordFields"

 

My understanding is that Record.FieldNames should return a list of the Field Names in each row. However, the function fails with "Can't Covert <ColumnName> to type Record. I'm assuming that means that it is not liking the result coming from Record.FieldNames. I assume I need a list function here, but not sure what. I do know that I eventually want to use Table.ExpandListColumn to generate a row for each Field name.

1 ACCEPTED SOLUTION
KT_Bsmart2gethe
Super User
Super User

Hi @Anonymous ,

 

The syntax for look like > Record.FieldNames([ColumnNames]). To have your syntax work, you need to update your syntax to Record.FieldNames(Record.Field(_,ColumnNames)).

 

Regards

KT

View solution in original post

3 REPLIES 3
KT_Bsmart2gethe
Super User
Super User

Hi @Anonymous ,

 

The syntax for look like > Record.FieldNames([ColumnNames]). To have your syntax work, you need to update your syntax to Record.FieldNames(Record.Field(_,ColumnNames)).

 

Regards

KT

Anonymous
Not applicable

Brilliant! At first pass that seems to do what I need it to!

 

So basically, I need to get the record, to then extract the fields? Can you explain the underscore syntax, is that a shortcut for basically saying use the current record?

 

Cheers and thanks!!

Hi @Anonymous ,

 

When you deal with dynamic column names, it either comes as "ColumnName" or [ColumnName].

when you declare the variable as a text, it works for syntax "ColumnName" but not within the square bracket. So, you need Record.Field(_, "ColumnNames"). The underscore means each of the fields within a record.

I hope above explainable is clear for you 🙂

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Kudoed Authors