Forum Discussion

Umadhandapani's avatar
Umadhandapani
Helper II
4 years ago
Solved

Using Power Query / Advanced Editor

Hi, This is my JSON data, I need to transform the data using Power Query in the following format.   Name Date Amount AA 2022-1-24 100 BB 2022-1-24 200  
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    You have to put additional columns in to 'group' the records.  As a human you can look at that data as it is and see patterns but power query can't so here's the algorithm:

    1. Add an index column (from the interface)

    2. Add a custom column using Number.IntegerDivide , Index and 2 to make a column which 'groups' the data.

    3. Remove the Index column (from step 1)

    4. Pivot the fields.fieldname column with fieldvalue as values using Don't aggregate

     

    Let me know how it goes

HotChilli's avatar
HotChilli
Community Champion
4 years ago

You have to put additional columns in to 'group' the records.  As a human you can look at that data as it is and see patterns but power query can't so here's the algorithm:

1. Add an index column (from the interface)

2. Add a custom column using Number.IntegerDivide , Index and 2 to make a column which 'groups' the data.

3. Remove the Index column (from step 1)

4. Pivot the fields.fieldname column with fieldvalue as values using Don't aggregate

 

Let me know how it goes