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.
I've been trying for months to figure this out. Without expanding the columns (because it creates duplicate rows of my primary key field), I'd like to extract various field values which reside in a record column, within a list column, within a record column, within a list column, within a record column, within a list column.
I do not want to expand the columns. I just want to extract the values into a custom column. If I could expand to new columns instead of rows, that would solve everything. I want [txhash] to remain a field with distinct values. When you get to the bottom of list, there are various key & value attribute pairs. The value in the [key] field, I want as the column header. The value in the [value] field should be the value in the respective key column it is paired with.
In its simplest form, I want to expand all the bottom level values to new columns instead of rows. To add to the complexity, the parent list creates multiple sub list having similar key names. I am not sure how to handle this and its driving me nuts.
I'm thinking I need something like this below. A bunch of nested List.Transform & Record.Field functions to dive into the List/Record,List/Record,List/Record,Attribute Pairs... What am I doing wrong here?
= Table.AddColumn(#"PreviousStep",List.Transform([Column1.logs], each Record.Field([Column1.logs], List.Transform([Column1.logs.events], each Record.Field([Column1.logs.events], List.Transform([Column1.logs.events.attributes], each Record.Field([Column1.logs.events.attributes],[Value]))))))
This is what the structure looks like.
I want to get the JSON into this format.
TxHash | Transfer.Recipent | Transfer.Sender | Transfer.Amount | withdraw_rewards.amount | withdraw_rewards.validator | withdraw_rewards.delegator |
asdfghjkl | 1234567 | 345678 | 500 | 450 | 56789 | 7890 |
When I expand the lists and records, it does this. As you can see, the TxHash repeats. I want the list of values to be columns instead of rows.
TxHash | type | Key | Value |
asdfghjkl | transfer | recipient | 1234567 |
asdfghjkl | transfer | sender | 345678 |
asdfghjkl | transfer | amount | 500 |
asdfghjkl | withdraw_rewards | amount | 450 |
asdfghjkl | withdraw_rewards | validator | 56789 |
asdfghjkl | withdraw_rewards | delegator | 7890 |
Below is a clip of the JSON
Hi @bigshooTer ,
Think you are pretty close, try these steps:
1. Merge the Type and Key columns
2. Select the (new) Merged column and choose "Pivot Column" on the Transform tab
3. In the dialog box set "Value" as value column and under advanced set "Don't aggregate"
I hope this is helpful
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.