Multiple Multi Level Nested Lists in JSON to Columns

[bigshooTer]

I must have read every thread on here, stack overflow, powerbi community but I am still struggling with this.  I have invested well over 20-30 hours in trying to figure this out. 

I have a rest api as my data source.  It contains multiple lists with records in them.  there are also lists within lists with records, and records within records.   You name it, it exists.

The api is list of transactions.  The fields are simply groups of Key & Attribute lists containing addtional information about the transactions.  In the end, there is 1 transaction so I want 1 record per transaction.  

In a perfect world, I would expand to new columns instead of expand to new rows if that feature was offered.  Essentially, I want to do Index Match or vlookup.  Merge will not work for me.  I've tried creating a query with just column headers as a mod suggested but I can't figure out how to populate the data under the column headers.   I've tried to use table.records with a looping offset but I can't figure out the context.  Every post I find has a variation that doesn't apply to my situation or the suggested solution is so complicated I can't interpret how to replicate it.

I just want a column for each key.  Then put the value in the column of that key.  I would need to match 3 fields.  txhash, events, and logs.events.attributes.key.  Then return logs.events.attributes.value of that record.

You can see here that I have individual list of transactions

When i expand the list, I now get 30 repeating rows for each transaction.  Within each list is this:

Now I would be fine with deleting these columns because when expanded they provide me no valuable information needed.  BUt i need to go deeper to get what i need within the records in the list.

Below we go into the records to find another list.

Within the list, more records!!

Now were at the bottom.

I want these keys to be columns with a single tx hash record in the data set.  I've tried group by, i've tried creating a separate query to create key headers, but i can't figure out how to populate the data with a table.record offset loop.

Any help would be greatly appreciated?  Below is what the tree looks like.

[rubayatyasmin]

Hi, @bigshooTer 

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

2. Expand Transactions List: In the Power Query Editor, find the "transactions" list, and expand it to get the individual transaction records.

3. Expand Events List: Expand the "events" list within each transaction to get separate rows for each event.

4. Expand Logs List: Expand the "logs" list within each event to get separate rows for each log.

5. Expand Attributes List: Expand the "attributes" list within each log to get separate rows for each attribute.

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

7. Remove Unnecessary Columns: After pivoting, you'll have one column for each unique "key." Remove any columns that are not needed.

8. Grouping: Group the data by "txhash" and "event name" to combine the attributes for each event into a single row.

9. Aggregate the Data: Use aggregation functions (e.g., Max, Min, First, Last) to combine the attribute values into one row per transaction and event.

10. Data Type Handling: Ensure that the data types of the columns are appropriate for further analysis in Power BI.

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

[bigshooTer]

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?