Using Power Query / Advanced Editor

[Umadhandapani]

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

[HotChilli · Accepted solution]

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]

@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.