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 experts - I'm brand new to PowerBI / PowerQuery and have been struggling with this for a couple of days - hoping someone can point me in the right direction.
I'm querying a JSON API for some custom form data. The API returns 3 types of form objects, the third of which can have multiple instances (obfuscating the name of the field to keep things anonymous):
TimeSpent
BackendFields
Form3
Form3
Form3
This results in the following table in PowerBI (hidden fields are all type Form3):
Contents of Form3.CustomForm.FieldValues is a list of Records like this:
Each instance of Form 3 has 4 records, with 4 fields in each record:
- FieldName
- FieldDisplayName
- FieldLabel
- Value
e.g.
The FieldName values are always the same for each instance of Form 3, Value values differ each time.
My task is to get all data from the records into a new table, 1 row per instamce. The FieldName from the records (always the same) as the header and the Value as the value on each row:
(all.record1.FieldName) | (all.record2.FieldName) | (all.record3.FieldName) | (all.record4.FieldName) |
(instance1.record1.FieldValue) | (instance1.record2.FieldValue) | (instance1.record3.FieldValue) | (instance1.record4.FieldValue) |
(instance2.record1.FieldValue) | (instance2.record2.FieldValue) | (instance2.record3.FieldValue) | (instance2.record4.FieldValue) |
I've tried expanding the column of Lists (with records per list) both ways (new rows/extract values), but neither lets me expand the record fields to columns.
Any ideas?
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.