Forum Discussion
Expanding JSON column results in adding rows
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
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
3 Replies
You can adapt it like this to do that.
= Text.Combine(List.Transform([hiring_managers], each Record.Field(_, "name") & ", " & Record.Field(_, "primary")), "; ")
Pat
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
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
