Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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
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