Power Query varying column names

BINavPete · November 12, 2023, 6:23pm

I am importing data from a JSON file to import form data from a questionnaire via an API.
The questionaire has a series of yes/no medical questions. When yes is selected a details box opens up to request detail to be input for the question.

This arrives in PQ as a record. When Yes is selected the details appear as a record col header. But when No is selected my expectation is a null value for that column in the record, but I’m not getting any record column at all when expanding the record

So how do I edit the record in PQ to include all possible column headers.
.

If I want to add column “Test” to the record, how do I check if it’s already in the record and if not then add it?

Thanks
Pete

BINavPete · November 13, 2023, 10:43am

Hi All
I managed to fix this.
Steps were

For each row convert record to table, transpose table and promote col headers
Expand table columns dynamically using Chandeep’s explanation here.
https://goodly.co.in/expand-all-columns-dynamically-power-query/
Create table with all possible headers and append

Thanks to those who had a reccy

Pete