M Language question: A Column contains a List and a Record both types

[ranbeermakin]

Hi all,

I'm using pagination to get data from a REST API. The API works fine but results in like this:

As you see the engagement column contains a List and a Record as well. I would like to convert that record to a list. Any clue how to do that? 

After that I can use Table.ExpandListColumn and my code will work fine.

Appreciate any help in this.

Thanks

Ranbeer

[OwenAuger · Accepted solution]

Hi Ranbeer,

You can use Value.Is to check if the values in the column are of type record, and if so use Record.ToList to convert the record to a list of field values.

Here's some code that worked for me:

= Table.TransformColumns( PreviousStep, { "engagement", each if Value.Is( _, type record ) then Record.ToList( _ ) else _ } )

Another option is:

= Table.TransformColumns( PreviousStep, { "engagement", each try Record.ToList( _ ) otherwise _ } )

Regards

Owen

[OwenAuger · Accepted solution]

Hi Ranbeer,

You can use Value.Is to check if the values in the column are of type record, and if so use Record.ToList to convert the record to a list of field values.

Here's some code that worked for me:

= Table.TransformColumns( PreviousStep, { "engagement", each if Value.Is( _, type record ) then Record.ToList( _ ) else _ } )

Another option is:

= Table.TransformColumns( PreviousStep, { "engagement", each try Record.ToList( _ ) otherwise _ } )

Regards

Owen

[ranbeermakin]

Thanks Owen.

But, what I really wanted was to put that record in a list, and not to convert the record to list of values.

Something like

Current situation:

[Record]

New situation

{List}

Expanding this list should give me the above "[Record]"

[Record]

Do you know how to do that?

Thanks for help.

[ranbeermakin · Accepted solution]

Ok, I was able to do this like

Table.TransformColumns( previousStep, { "engagement", each if Value.Is( _, type record ) then { _ } else _ } )

[OwenAuger]

Ah gotcha - yes if you just want a record turned into a list containing that record then your solution is perfect 😀