Forum Discussion
Umadhandapani
Helper II
Helper IIUsing 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
amitchandak
Super User
Super UserUmadhandapani , 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.