Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Can someone help me to understand how I have to process this json result from my IP.
"result": [
{
"metricId": "actionsPerSession",
"data": [
{
"dimensions": [
"APPLICATION-A"
],
"dimensionMap": {
"dt.entity.application": "APPLICATION-A"
},
"timestamps": [
1614556800000,
1617235200000,
1619827200000,
1622505600000,
1625097600000
],
"values": [
21.28073384020771,
47.06336163296851,
11.023587012711282,
10.320278490922409,
10.740145332074341
]
}
]
},
{
"metricId": "actionCount",
"data": [
{
"dimensions": [
"APPLICATION-A"
],
"dimensionMap": {
"dt.entity.application": "APPLICATION-A"
},
"timestamps": [
1614556800000,
1617235200000,
1619827200000,
1622505600000,
1625097600000
],
"values": [
6707,
28477.333333333332,
144980,
859878.6666666666,
798342.6666666666
]
}
]
}
]
The end-result should look something like:
application | timestamp | action-per-session | actionCount |
APPLICATION-A | 1614556800000 | 21.28073384020771 |6707|
APPLICATION-A | 1617235200000 | 47.06336163296851 |28477.333333333332|
Any help is much appreciated.
Solved! Go to Solution.
Hi @SysLostInBI
It seems that your json code will return many lists. Firsly, get data in Power BI Desktop by Json connector to connect to the Json File or web to connect to the API to get Json File. Then you need to transform the json file to Power BI Table.
For reference: Converting JSON data into PowerBI table
Using Power BI with JSON Data Sources and Files
When you connect to Json, you need to transform your table to the result as below.
Here I get data from Json to have a test.
let
Source = Json.Document(File.Contents("......")),
result = Source[result],
result1 = result{0},
#"Converted to Table" = Record.ToTable(result1),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type number}, {"Value.3", type number}, {"Value.4", type number}, {"Value.5", type number}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"dimensions", type text}, {"timestamps", Int64.Type}, {"action-per-session", type number}, {"actionCount", type number}}),
#"Filled Down" = Table.FillDown(#"Changed Type1",{"dimensions"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Filled Down",{{"timestamps", type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"action-per-session", type text}, {"actionCount", type text}}, "en-US"),{"dimensions", "timestamps", "action-per-session", "actionCount"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Finally Select all rows , right click and use merge function in Power Query.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SysLostInBI
It seems that your json code will return many lists. Firsly, get data in Power BI Desktop by Json connector to connect to the Json File or web to connect to the API to get Json File. Then you need to transform the json file to Power BI Table.
For reference: Converting JSON data into PowerBI table
Using Power BI with JSON Data Sources and Files
When you connect to Json, you need to transform your table to the result as below.
Here I get data from Json to have a test.
let
Source = Json.Document(File.Contents("......")),
result = Source[result],
result1 = result{0},
#"Converted to Table" = Record.ToTable(result1),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type number}, {"Value.3", type number}, {"Value.4", type number}, {"Value.5", type number}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"dimensions", type text}, {"timestamps", Int64.Type}, {"action-per-session", type number}, {"actionCount", type number}}),
#"Filled Down" = Table.FillDown(#"Changed Type1",{"dimensions"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Filled Down",{{"timestamps", type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"action-per-session", type text}, {"actionCount", type text}}, "en-US"),{"dimensions", "timestamps", "action-per-session", "actionCount"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Finally Select all rows , right click and use merge function in Power Query.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
15 | |
4 | |
3 | |
3 | |
2 |