Transform json list of record with dynamic keys to table

[mukuljp]

I'm getting a JSON response from an m-query with below format

const json = [
{
"id":"a",
"values":{"c":1,"d":"2","e":"3"}
},
{
"id":"b",
"values":{"f":4,"g":"5","h":"6"}
}
]

I want to convert this to table like below,

id	key	value
a	c	1
a	d	2
a	e	3
b	f	4
b	g	5
b	h	6

Can any one redirect me to a sample m query to transofm dynamic key value json objects maps

[mukuljp]

@DataVitalizer This works, Thanks for sharing

[DataVitalizer · Accepted solution]

Hi @mukuljp 

After connecting your Json file, open the powerquery editor and follow these steps:

1. Right select the column "id" and click Unpivot other columns
2. Right Click second column "Attribute" and replace Values. by none

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated