Announcing Power Query Data Types
Power Query is a powerful and time-saving tool used to gather and analyze large sets of data by organizing them in a way that will make its consumption in Excel much easier. With this feature, you can create custom data types from any and load them in the Excel grid as rich values.
How to convert JSON data into Excel table in Power Query
I would like to import the JSON file into Excel, I am using Power Query as I'm not versed in VBA. I would like the output to look like this: The issue I'm having is figuring out how to to transform the data based on my data I have. Below is sample data: { "items": [ { "tableName": "tableA", "count": 1729, "columnNames": [ "id", "createdTime", "updatedTime", "name", "category", "id2", "subject", "text" ], "rows": [ [ "1234567", "2019-02-02T14:54:02Z", "2019-02-02T20:57:32Z", "John Smith", "123", "1234", "Product A - need assistance ", "text field" ], [ "2345678", "2019-02-03T14:54:02Z", "2019-02-03T20:57:32Z", "John Smith", "123", "1234", "Product B help", "Text field" ], etc } The first step was converting the json file to a table, afterwards, I expanded 'value' cell to new rows and did so another time, to get thisimage: From there I further expanded and to get all the values. I tried removing duplicates and transpose but those options are leading to the tabular data I am looking for. How do I go about solving this issue? Thanks for your time.
