How can I turn the deepest elements of nested JSON payload into individual rows in Power Query?

Question

Goal: I have a JSON payload with the following format:

    {
        "Values": [
        {
            "Details": {
                "14342": {
                    "2016-06-07T00:00:00": {
                        "Value": 99.62,
                        "Count": 7186
                    },
                    "2016-06-08T00:00:00": {
                        "Value": 99.73,
                        "Count": 7492
                    }
                },
                "14362": {
                    "2016-06-07T00:00:00": {
                        "Value": 97.55,
                        "Count": 1879
                    },
                    "2016-06-08T00:00:00": {
                        "Value": 92.68,
                        "Count": 355
                    }
                }
            },
            "Key": "query5570027",
            "Total": 0.0
        },
        {
           "Details": {
                "14342": {
                    "2016-06-07T00:00:00": {
                        "Value": 0.0,
                        "Count": 1018
                    },
                    "2016-06-08T00:00:00": {
                        "Value": 0.0,
                        "Count": 1227
                    }
                }
            },
            "Key": "query4004194",
            "Total": 0.0
        }
    ],
    "LatencyInMinute": 0.0
}

I want to load this in PowerBI and produce a table like so:

Notice how each Value + Count pair has its own row and some elements are repeated.

Problem: When I try to do this in Power BI (via Power Query), I get three initial columns, one of which is Details. Trouble is that I can expand Details, but I just get more columns, where what I really want is rows. I tried transpose, pivoting columns, and such but nothing helped. My troubles are exacerbated by Power Query treating the nested data elements as column names.

Question: Is there a way, in M, to convert this nested JSON payload to the table example I illustrated above?

Mike Honey Mike Honey · Accepted Answer · 2016-06-09T22:27:35

Chris Webb wrote a recursive function to expand all table-type columns - I've managed to clone it for record-type columns:

https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b

How can I turn the deepest elements of nested JSON payload into individual rows in Power Query?

2 Answers