JSON API RESULTS columns to rows

[lbown]

Hi

I am calling a JSON API with multiple dimensions and getting results in the following format:

{
  "metrics" : [ "FORECAST_ROOMS", "FORECAST_ADR", "FORECAST_REVENUE" ],
  "dimensions" : [ "MARKET_SEGMENT", "TARGET_DATE" ],
  "dimValues" : {
    "MARKET_SEGMENT" : [ "sg3278", "sg3278", "sg3278", "sg3278", "sg3278", "sg3278", "sg3278", "sg3278", "sg3278", "sg3280", "sg3280", "sg3280", "sg3280", "sg3280", "sg3280", "sg3280", "sg3280", "sg3280" ],
    "TARGET_DATE" : [ "2019-06-01", "2019-06-02", "2019-06-03", "2019-06-04", "2019-06-05", "2019-06-06", "2019-06-07", "2019-06-08", "2019-06-09", "2019-06-01", "2019-06-02", "2019-06-03", "2019-06-04", "2019-06-05", "2019-06-06", "2019-06-07", "2019-06-08", "2019-06-09" ]
  },
  "metricValues" : {
    "FORECAST_ROOMS" : [ 161, 153, 147, 156, 150, 155, 160, 161, 173, 0, 0, 0, 0, 0, 0, 0, 0, 0 ],
    "FORECAST_ADR" : [ 159.6239751552795, 128.72235294117647, 159.5944217687075, 184.66961538461538, 189.7738, 162.23774193548385, 136.878875, 159.63416149068323, 130.03739884393065, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 ],
    "FORECAST_REVENUE" : [ 25699.46, 19694.52, 23460.38, 28808.46, 28466.07, 25146.85, 21900.62, 25701.1, 22496.47, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 ]
  }

I need to get the data into a table similar to the below:

Target_Date, Market_Segment, Forecast_Rooms, Forecast_ADR, forecast Revenue,
01/06/2019,Sg_3278, 161, 159.62, 25699.46
01/06/2019,Sg_3280, 0, 0, 0
02/06/2019, Sg_3278, 153, 128.72, 19694.52
02/06/2019, Sg_3280, 0, 0, 0

How can I transform the results?

[ImkeF · Accepted solution]

Shortest way is to use some record functions like so:

let
    YourJSON = "{#(cr)#(lf)  ""metrics"" : [ ""FORECAST_ROOMS"", ""FORECAST_ADR"", ""FORECAST_REVENUE"" ],#(cr)#(lf)  ""dimensions"" : [ ""MARKET_SEGMENT"", ""TARGET_DATE"" ],#(cr)#(lf)  ""dimValues"" : {#(cr)#(lf)    ""MARKET_SEGMENT"" : [ ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3278"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"", ""sg3280"" ],#(cr)#(lf)    ""TARGET_DATE"" : [ ""2019-06-01"", ""2019-06-02"", ""2019-06-03"", ""2019-06-04"", ""2019-06-05"", ""2019-06-06"", ""2019-06-07"", ""2019-06-08"", ""2019-06-09"", ""2019-06-01"", ""2019-06-02"", ""2019-06-03"", ""2019-06-04"", ""2019-06-05"", ""2019-06-06"", ""2019-06-07"", ""2019-06-08"", ""2019-06-09"" ]#(cr)#(lf)  },#(cr)#(lf)  ""metricValues"" : {#(cr)#(lf)    ""FORECAST_ROOMS"" : [ 161, 153, 147, 156, 150, 155, 160, 161, 173, 0, 0, 0, 0, 0, 0, 0, 0, 0 ],#(cr)#(lf)    ""FORECAST_ADR"" : [ 159.6239751552795, 128.72235294117647, 159.5944217687075, 184.66961538461538, 189.7738, 162.23774193548385, 136.878875, 159.63416149068323, 130.03739884393065, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 ],#(cr)#(lf)    ""FORECAST_REVENUE"" : [ 25699.46, 19694.52, 23460.38, 28808.46, 28466.07, 25146.85, 21900.62, 25701.1, 22496.47, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 ]#(cr)#(lf)  }}#(cr)#(lf)",
    #"Parsed JSON" = Json.Document(YourJSON),
    CombineRecordsWithValues = Record.Combine({#"Parsed JSON"[dimValues], #"Parsed JSON"[metricValues]}),
    CreateTables = Table.FromColumns(Record.FieldValues(CombineRecordsWithValues), Record.FieldNames(CombineRecordsWithValues))
in
    CreateTables