Working with JSON and multiple List

[SysLostInBI]

I'm sure this is a very basic question, so I feel bad bothering you with this.

But I'm stuck with this.

I have imported a JSON file:

{
    "result": [
        {
            "metricId": "xx",
            "data": [
                {
                    "dimensions": [
                        "APPLICATION-aa",
                        "SATISFIED"
                    ],
                    "timestamps": [
                        1585872000000,
                        1586044800000
                    ],
                    "values": [
                        241,
                        1067
                    ]
                },
                {
                    "dimensions": [
                        "APPLICATION-bb",
                        "FRUSTRATED"
                    ],
                    "timestamps": [
                        1585872000000,
                        1586044800000
                    ],
                    "values": [
                        172,
                        771
                    ]
                }
            ]
        }
    ]
}

After 

• Converting to table
• Transpose table
• Promote Header (& Change Type)
• Expand the Result
• Expand result.data
• Expand result.data

My imported data has become:

 Now my next step is to "Extract Values" from result.data.dimension while adding a delimiter and then split the table by delimiter.

The next step would be to expand the list of result.data.timestamps together with the result.data.values.
Which should give me something like:

result.metricID	result.data.dimension.1	result.data.dimension.2	result.data.timestamps	result.data.values
xx	Application-aa	SATISFIED	1.58587E+12	241
xx	Application-aa	SATISFIED	1.58604E+12	1067
xx	Application-bb	FRUSTRATED	1.58587E+12	172
xx	Application-bb	FRUSTRATED	1.58604E+12	771

Thanks for your help!

[Mariusz]

Hi @SysLostInBI 

Try this steps

let
    Source = Json.Document(File.Contents("C:\Users\mrepczynski\OneDrive - Network Homes\Desktop\test.json")),
    result = Source[result],
    result1 = result{0},
    data = result1[data],
    Custom1 = Table.FromRecords( data ),
    #"Transposed Table" = Table.Transpose(Custom1),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each List.Combine( { [Column1], [Column2] } )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    Custom = Table.FromRows( List.Zip( #"Removed Other Columns"[Custom] ) )
in
    Custom

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[FrankAT]

Hi @SysLostInBI 

I think your json file describes 16 records: 2 dimensions x 2 timestamps x 2 values => 2 x 2 x 2 = 8 and that 2 times.

So the expanded result in Power Query is:

If your json file looks as following you get the expected result.

{
 "result": [
  {
   "metricId": "xx",
   "data": [
    {
     "dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1585872000000, "values":241
    },
    {
     "dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1586044800000, "values":1067
    },
    {
     "dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1585872000000, "values":172
    },
    {
     "dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1586044800000, "values":771
    }
   ]
  }
 ]
}

Regards FrankAT

[SysLostInBI]

Thanks, but no. The JSON is actually formed as I described it. 

It originates from a commercial product and I have to deal with it 🙂

[amitchandak]

@SysLostInBI , refer if this can help

https://www.youtube.com/watch?v=ipI6mrWLQKA

https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...

[SysLostInBI · Accepted solution]

Many thanks. I actually watched the first video you are referencing just before posting this question 🙂 it does not contain the answer sadly.
I also watched https://www.youtube.com/watch?v=-QO57RHzxus which was quite helpful for the first few steps.

Now, after posting this I just found this:
https://community.powerbi.com/t5/Desktop/How-to-expand-multiple-columns-to-new-rows-at-the-same-time...

Which seems to solve my problem!