Duplicate rows when expanding JSON list

[dinosainsburys]

Hi,

I am currently pulling data from an API where it nests the timestamps and the values together. I'm not sure how to tackle this as I have been trying for hours with another (similar) dataset I have

I have created an example here:

So the two computers will each have 31 timestamps which each correspond to 31 values.

So I expand the rows on timestamps and it spits out 60 for each computer, great! Then I do it for values and it balloons and it size and is no longer valid. I have tried playing around with grouping it and using an index column but could not get it to display correctly. Any ideas?

I a,m unable to include a sample PBI file, as I am still new and my organisation prohibits 3rd party so I will include some sample JSON instead which is similar to my use-case:

{
  "result": [
    {
      "metricId": "HOST(\"windows.host\")",
      "dataPointCountRatio": 0.000303,
      "dimensionCountRatio": 0.00202,
      "data": [
        {
          "dimensions": [
            "COMPUTER-ABC1234567"
          ],
          "dimensionMap": {
            "dt.entity.host": "COMPUTER-ABC1234567"
          },
          "timestamps": [
            1723680000000,
            1723766400000,
            1723852800000,
            1723939200000,
            1724025600000,
            1724112000000,
            1724198400000,
            1724284800000,
            1724371200000,
            1724457600000,
            1724544000000,
            1724630400000,
            1724716800000,
            1724803200000,
            1724889600000,
            1724976000000,
            1725062400000,
            1725148800000,
            1725235200000,
            1725321600000,
            1725408000000,
            1725494400000,
            1725580800000,
            1725667200000,
            1725753600000,
            1725840000000,
            1725926400000,
            1726012800000,
            1726099200000,
            1726185600000,
            1726272000000
          ],
          "values": [
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            803.25,
            null,
            null,
            null,
            null,
            null,
            null,
            740.25
          ]
        },
        {
          "dimensions": [
            "COMPUTER-XYZ9876543"
          ],
          "dimensionMap": {
            "dt.entity.host": "COMPUTER-XYZ9876543"
          },
          "timestamps": [
            1723680000000,
            1723766400000,
            1723852800000,
            1723939200000,
            1724025600000,
            1724112000000,
            1724198400000,
            1724284800000,
            1724371200000,
            1724457600000,
            1724544000000,
            1724630400000,
            1724716800000,
            1724803200000,
            1724889600000,
            1724976000000,
            1725062400000,
            1725148800000,
            1725235200000,
            1725321600000,
            1725408000000,
            1725494400000,
            1725580800000,
            1725667200000,
            1725753600000,
            1725840000000,
            1725926400000,
            1726012800000,
            1726099200000,
            1726185600000,
            1726272000000
          ],
          "values": [
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            713
          ]
        }
      ]
    }
  ]
}

[lbendlin · Accepted solution]

List.Zip is your friend.

let
    Source = Json.Document("{
  ""result"": [
    {
      ""metricId"": ""HOST(\""windows.host\"")"",
      ""dataPointCountRatio"": 0.000303,
      ""dimensionCountRatio"": 0.00202,
      ""data"": [
        {
          ""dimensions"": [
            ""COMPUTER-ABC1234567""
          ],
          ""dimensionMap"": {
            ""dt.entity.host"": ""COMPUTER-ABC1234567""
          },
          ""timestamps"": [
            1723680000000,
            1723766400000,
            1723852800000,
            1723939200000,
            1724025600000,
            1724112000000,
            1724198400000,
            1724284800000,
            1724371200000,
            1724457600000,
            1724544000000,
            1724630400000,
            1724716800000,
            1724803200000,
            1724889600000,
            1724976000000,
            1725062400000,
            1725148800000,
            1725235200000,
            1725321600000,
            1725408000000,
            1725494400000,
            1725580800000,
            1725667200000,
            1725753600000,
            1725840000000,
            1725926400000,
            1726012800000,
            1726099200000,
            1726185600000,
            1726272000000
          ],
          ""values"": [
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            1512,
            803.25,
            null,
            null,
            null,
            null,
            null,
            null,
            740.25
          ]
        },
        {
          ""dimensions"": [
            ""COMPUTER-XYZ9876543""
          ],
          ""dimensionMap"": {
            ""dt.entity.host"": ""COMPUTER-XYZ9876543""
          },
          ""timestamps"": [
            1723680000000,
            1723766400000,
            1723852800000,
            1723939200000,
            1724025600000,
            1724112000000,
            1724198400000,
            1724284800000,
            1724371200000,
            1724457600000,
            1724544000000,
            1724630400000,
            1724716800000,
            1724803200000,
            1724889600000,
            1724976000000,
            1725062400000,
            1725148800000,
            1725235200000,
            1725321600000,
            1725408000000,
            1725494400000,
            1725580800000,
            1725667200000,
            1725753600000,
            1725840000000,
            1725926400000,
            1726012800000,
            1726099200000,
            1726185600000,
            1726272000000
          ],
          ""values"": [
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            744,
            713
          ]
        }
      ]
    }
  ]
}"),
    result = Source[result],
    result1 = result{0},
    data = result1[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Records"}}),
    #"Expanded Records" = Table.ExpandRecordColumn(#"Renamed Columns", "Records", {"dimensions", "timestamps", "values"}, {"dimensions", "timestamps", "values"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Records", {"dimensions", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom" = Table.AddColumn(#"Extracted Values", "Custom", each List.Zip({[timestamps],[values]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"dimensions", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Extracted Values1" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Custom", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"timestamp", "value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"timestamp", Int64.Type}, {"value", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [timestamp],each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[timestamp]/1000),Replacer.ReplaceValue,{"timestamp"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"timestamp", type datetime}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[dinosainsburys]

My bad! I missed a step, works now!

Thanks so much, was trying to figure this out for hours! 🙂

[dinosainsburys]

Hey, so I think this almost works but when I apply it to my usecase I get the following error:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]