Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lbown
Frequent Visitor

JSON API RESULTS columns to rows

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?

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

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

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Community Champion
Community Champion

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

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.