Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |