Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I am having some problems in plotting the data from dynatrace API. Indeed they come in a particular json format, like the following:
"data": [
{
"dimensions": [
"HOST-177FFDC0829EAC9C"
],
"dimensionMap": {
"dt.entity.host": "HOST-177FFDC0829EAC9C"
},
"timestamps": [
1709648340000,
1709648400000,
1709648460000,
1709648520000,
1709648580000,
1709648640000,
1709648700000,
1709648760000,
1709648820000,
1709648880000,
1709648940000,
1709649000000,
1709649060000,
1709649120000,
1709649180000,
1709649240000
],
"values": [
100,
100,
100,
100,
100,
100,
99.99674986985822,
100,
99.99343512952328,
100,
100,
100,
99.99348439400394,
100,
null,
null
]
},
and I am not able to transform it properly in power BI.
I attach the pbix file (the table I am interested in is "host_cpu_lab".
Can you show me please how to transform the data so as to be able to plot it in a line chart?
Thank you
In the end, I managed to achieve my goal by splitting the dataset in two parts and assigning and adding to each part an index column
Hi @PowerBI88 ,
Please try the following methods and check if they can solve your problem:
1.Expand the data column to rows in Power Query Editor.
2.Enter the following statement in the Advanced Editor.
let
Source = Json.Document(File.Contents("C:\Users\v-huizhn\Downloads\generated.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UserId", "id", "tipSecurableSet"}, {"Column1.UserId", "Column1.id", "Column1.tipSecurableSet"}),
#"Expanded Column1.tipSecurableSet" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.tipSecurableSet", {"id", "Clients"}, {"Column1.tipSecurableSet.id", "Column1.tipSecurableSet.Clients"}),
#"Expanded Column1.tipSecurableSet.Clients" = Table.ExpandListColumn(#"Expanded Column1.tipSecurableSet", "Column1.tipSecurableSet.Clients"),
#"Expanded Column1.tipSecurableSet.Clients1" = Table.ExpandRecordColumn(#"Expanded Column1.tipSecurableSet.Clients", "Column1.tipSecurableSet.Clients", {"@odata.type", "typeId", "Entityids", "Clientid"}, {"Column1.tipSecurableSet.Clients.@odata.type", "Column1.tipSecurableSet.Clients.typeId", "Column1.tipSecurableSet.Clients.Entityids", "Column1.tipSecurableSet.Clients.Clientid"})
in
#"Expanded Column1.tipSecurableSet.Clients1"
3.You can also view the following link to learn more about transform json data.
Solved: Converting JSON data into PowerBI table - Microsoft Fabric Community
Solved: Convert json List Record to Table value - Microsoft Fabric Community
4.Only the super user can attach the pbix files. You can use the following method to provide the sample data.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
how can I attach the pbix file to my post?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
64 | |
45 | |
40 | |
40 |