Convert JSON that contains {name: XXX; value : YYYY} into table where colum = XXX and row =YYY

[ochaoui]

Hi Folks,

I need your help concerning the import of this JSON in PowerBI using Power Query :

{
    "Response": {
        "TimeUnit": [
            1619395200000,
            1619308800000,
            1619222400000,
            1619136000000
        ],
        "stats": {
            "data": [
                {
                    "identifier": {
                        "names": [
                            "developer_app",
                            "api_product"
                        ],
                        "values": [
                            "XXXX",
                            "aaaa"
                        ]
                    },
                    "metric": [
                        {
                            "values": [
                                5.0,
                                0.0,
                                6.0,
                                1.0
                            ]
                        }
                    ]
                },
                {
                    "identifier": {
                        "names": [
                            "developer_app",
                            "api_product"
                        ],
                        "values": [
                            "YYYY",
                            "bbbb"
                        ]
                    },
                    "metric": [
                        {
                            "values": [
                                1.0,
                                0.0,
                                0.0,
                                1.0
                            ]
                        }
                    ]
                }
            ]
        }
    }
}

I would like to obtain from this JSON the following table :

Date	developer_app	api_product	Value
1619395200000	XXXX	aaaa	5
1619308800000	XXXX	aaaa	0
1619222400000	XXXX	aaaa	6
1619136000000	XXXX	aaaa	1
1619395200000	YYYY	bbbb	1
1619308800000	YYYY	bbbb	0
1619222400000	YYYY	bbbb	0
1619136000000	YYYY	bbbb	1

How can I do that using power query UI and/or advanced pane for coding ?

Thanks in advance for your help.

Regards,

[Vera_33 · Accepted solution]

Hi @ochaoui 

I guess I made too many steps, hope you can work from it, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZLRCsIgFIZfJbweoatG9RhRUMwR1k4gNCfTuom9e9qC1tLlRQRB/41wvt/D7/GkKbpQMTCiaAFKlkIBRfPBvdiAJS9gJbi2IH0AK5KQ2Wg2ibFV5GB4OvWxOI7HPkZGyQ3hB8qidialmVadpA3JmWaOpFaX11Jzh+cgND9wqBwtn62CFaA8/TtB4AzHUkK1ZVJSFL3zM8m3sirz095M2m/OehpRdGbHU2C+tVFILKPePG5UexpTVICu+P5NxJ4/aLoEP9RqMuwumUs4yJUEucgQ95s8Y7Oq3chxwzXk/5K3rRujgFg7o19fcvLBJQ9zfWnJn0stx52Yo0ZZdgU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([Column1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "Date", each [Custom][Response][TimeUnit]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "stats", each [Custom][Response][stats][data]),
    #"Expanded stats" = Table.ExpandListColumn(#"Added Custom4", "stats"),
    #"Added Custom1" = Table.AddColumn(#"Expanded stats", "Value", each  [stats][metric]{0}[values]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "identifier", each Record.FromList( List.Zip( Record.ToList( [stats][identifier])),{"develop_app","api_product"})),
    #"Expanded identifier" = Table.ExpandRecordColumn(#"Added Custom2", "identifier", {"develop_app", "api_product"}, {"develop_app", "api_product"}),
    #"Added Custom5" = Table.AddColumn(#"Expanded identifier", "Custom.1", each List.Zip({[Date],[Value]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Custom.1", "develop_app", "api_product"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom.1"),
    #"Expanded develop_app" = Table.ExpandListColumn(#"Expanded Custom.1", "develop_app"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded develop_app", each ([develop_app] <> "developer_app")),
    #"Expanded api_product" = Table.ExpandListColumn(#"Filtered Rows", "api_product"),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded api_product", each ([api_product] <> "api_product")),
    Custom1 = Table.TransformColumns( #"Filtered Rows1",{"Custom.1", each Record.FromList(_,{"Date","Value"})}),
    #"Expanded Custom.2" = Table.ExpandRecordColumn(Custom1, "Custom.1", {"Date", "Value"}, {"Date", "Value"})
in
    #"Expanded Custom.2"

[ochaoui]

Hi @Vera_33 !

Thank you for your answer ! Can you explain the steps you made to achieve this ?

And did you use the U to do it or is it full code ?

[Vera_33]

Hi @ochaoui 

At first I was clicking in the UI all the way down to see the data, but too many clicks! So I went back to modify some of them in the formula bar. You can paste the code in Advanced Editor then see each step on the right.