JSON data stored in 2 lists. 1 columns header, 1 data. Need to make into a table

[AshleyWells]

Hi all,

I was wondering if anyone has any experience in converting multiple rows of JSON data that is stored as 2 lists, 1 as column headers and the other as the data and transforming them into 1 table?

I have some experience with Power Query but really struggling with this.

When I parse the JSON data, I get this.

When I expand the record, I get this.

This gives me the column names and the data.

I'm struggling to transform this data for all rows into a table.

Can anyone help?

Thanks in advance 🙂

[ShirinArshadnia]

@AshleyWells 

you can use this refrence too

https://www.dutchdatadude.com/loading-multiple-json-files-using-power-query/

[m_dekorte]

Hi @AshleyWells ,

So does your ColumnData list contain a list for each of the columns in the same order as the ColumnNames?

If so try this

AddTable = Table.AddColumn( PrevStepName, "t", each 
    Table.FromColumns( [ColumnData], [ColumnNames], MissingField.UseNull )
)

If instead your ColumnData list contains a list for each row, again in the same order as the ColumnNames.

You can try this.

AddTable = Table.AddColumn( PrevStepName, "t", each 
    Table.FromRows( [ColumnData], [ColumnNames] )
)

I hope this is helpful

[AshleyWells]

Hi

I've tried using your solutions above and the closest I have got is the 2nd one but it's showing this

I've tried to expand the table "t" but it's not showing me any columns

Any ideas?

[m_dekorte]

Hi @AshleyWells,

Could you share an image of the contents of the [ColumnData]  column as well?

I've assumed it contained either a list with nested lists (one for each column header) in that case the list count for [ColumnData] and [ColumnHeaders] should be equal. OR it contained a list with nested lists (one for each row in the table) BUT it could also contain a list with nested records...

Can you drill down intoit and let met know what you find?

Thanks!

[AshleyWells]

Hi @m_dekorte 

I have to be carefull with this data as it's sensitive

Both [ColumnData] and [ColumnNames] should hold the same amount of data in each list.

In the bottom 2 rows of the [ColumnData] there is nested data but I don't need this extracting.

Thanks so much for your help so far

Ash

[v-jingzhang · Accepted solution]

Hi @AshleyWells 

Please add a custom column with this. Notice that there is a pair of {} around [ColumnData]. 

= Table.AddColumn(PreviousStep, "Custom", each #table([ColumnNames],{[ColumnData]}))

Then expand the custom table column. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AshleyWells]

@v-jingzhang that works perfectly, thanks so much!