Forum Discussion

AshleyWells's avatar
AshleyWells
Regular Visitor
3 years ago
Solved

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

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...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
AshleyWells
Regular Visitor
3 years ago

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's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      AshleyWells
      Regular Visitor
      3 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        3 years ago

        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.