Expanding JSON column results in adding rows

[jpt1228]

Hello, I have a JSON file from an API endpoint which has many nested columns. When I click hiring.team expand I get 3 columns which are hiring_managers, recruiters, coordinators. If I want to get the hiring managers name I click expand on the hiring manager column bit then get 3 records which then I can expand into names. I would like only 1 ID with the 3 hiring managers in a column with a delimiter so I can expand them to additional columns but retail only 1 id row.

I have tried to create severl reference queries to expand these and then merge them back on the id column but have not been successful.

Thanks

[mahoneypat]

You can adapt it like this to do that.

= Text.Combine(List.Transform([hiring_managers], each Record.Field(_, "name") & ", " & Record.Field(_, "primary")), "; ")

Pat

[mahoneypat · Accepted solution]

You can add a custom column with this formula to concatenate the recruiters.  

= Text.Combine(List.Transform([hiring_managers], each Record.Field(_, "name")), ", ")

Regards,

Pat

[jpt1228]

Thanks @mahoneypat What if in addition I wanted to extract "name" and "primary". Primary is another value in the nested column available if I expand "name" Ideally the customer column would be:

name1, primary1, name2, primary 2