Forum Discussion
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 |
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
2 Replies
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
Umadhandapani , Have you tried pivot after selecting the two columns. Use max as operation
https://radacad.com/pivot-and-unpivot-with-power-bi
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
