Forum Discussion
Multiple Multi Level Nested Lists in JSON to Columns
Hi, bigshooTer
Load the Data: In Power BI, go to the Power Query Editor by selecting "Transform data" in the Home tab. Then, connect to your JSON data source and load the data into Power Query.
Expand Transactions List: In the Power Query Editor, find the "transactions" list, and expand it to get the individual transaction records.
Expand Events List: Expand the "events" list within each transaction to get separate rows for each event.
Expand Logs List: Expand the "logs" list within each event to get separate rows for each log.
Expand Attributes List: Expand the "attributes" list within each log to get separate rows for each attribute.
Pivot the Data: Now, you should have a row for each attribute with columns "key" and "value." Pivot the "key" column to transform it into separate columns.
Remove Unnecessary Columns: After pivoting, you'll have one column for each unique "key." Remove any columns that are not needed.
Grouping: Group the data by "txhash" and "event name" to combine the attributes for each event into a single row.
Aggregate the Data: Use aggregation functions (e.g., Max, Min, First, Last) to combine the attribute values into one row per transaction and event.
Data Type Handling: Ensure that the data types of the columns are appropriate for further analysis in Power BI.
Load the Data: Load the transformed data back into Power BI.
These steps should help you flatten the nested JSON data and get it into a tabular format with columns representing each attribute key and one row per transaction and event combination. Please note that the exact steps and column names may vary based on your specific JSON structure.
Keep in mind that JSON data can vary in complexity, and depending on your specific data structure, you might need to adjust the steps accordingly. Also, consider writing custom M code in Power Query if needed, especially for complex JSON structures.
I should have mentioned that I'm not using Power BI, just Power Query for Excel. I have an access database (personal) with numerous sources of data. Quite a few are manual data loads with some manual transformation. I am trying to automate this process via rest api + data transformation/formatting in power query, then linking the table to an access db.
I believe I already have what you were trying to get to in step #7. I did it differently but if I'm following correctly you were looking to create a query that just has the indiviudal keys as column headers. See below.
I haven't used group yet. When you say group by txhash and event, do you mean in a separate query from where I have the attribute values as column headers?
I created my attribute headers table by Creating New Query using the logs events list after zooming to bottom levels.
Pivoting everything may be difficult with the amount of records I have. I assume you a referring to what is seen in my screenshot. In the end, I shouldn't have more than 10,000 distinct txhash's.
Can you pivot just an individual column? After drilling into all the records and lists, many are duplicates which seem to get created after expanding lists/records in the data set.
Another reason for the large amount of transactions at frist is because, there is 1 transaction type ("_MsgExec") where my address is in included in a batch transaction. This means the api returns trx lines for other accounts, as well, since we all share the same txhash for auto compound trx. For that particular type of transaction, I only want to pull the amount of coin received WHEN my address is equal to receiver address. ELI5: It's an auto-compound transaction where investors interest earned by muliple investors is compounded by the service provider. The details for each address included and the value which pertains to their related investment is found in logs event etc... There are quite a few other addresses in the trx. I believe this gets back to why the key-value attribute set is a list of records. There would be 1 record for each address. Only 1 record in the list would be mine, all others can be ignored (for this one type of trx).
I tried to pivot anyway but it's just spinning.. I think its just too many columns. Thank you for the step by step but it doesn't seem to work for me. Maybe its a difference between PowerBi Power Query and PQ for Excel?