Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm sure this is a very basic question, so I feel bad bothering you with this.
But I'm stuck with this.
I have imported a JSON file:
{
"result": [
{
"metricId": "xx",
"data": [
{
"dimensions": [
"APPLICATION-aa",
"SATISFIED"
],
"timestamps": [
1585872000000,
1586044800000
],
"values": [
241,
1067
]
},
{
"dimensions": [
"APPLICATION-bb",
"FRUSTRATED"
],
"timestamps": [
1585872000000,
1586044800000
],
"values": [
172,
771
]
}
]
}
]
}
After
My imported data has become:
Now my next step is to "Extract Values" from result.data.dimension while adding a delimiter and then split the table by delimiter.
The next step would be to expand the list of result.data.timestamps together with the result.data.values.
Which should give me something like:
result.metricID | result.data.dimension.1 | result.data.dimension.2 | result.data.timestamps | result.data.values |
xx | Application-aa | SATISFIED | 1.58587E+12 | 241 |
xx | Application-aa | SATISFIED | 1.58604E+12 | 1067 |
xx | Application-bb | FRUSTRATED | 1.58587E+12 | 172 |
xx | Application-bb | FRUSTRATED | 1.58604E+12 | 771 |
Thanks for your help!
Solved! Go to Solution.
Many thanks. I actually watched the first video you are referencing just before posting this question 🙂 it does not contain the answer sadly.
I also watched https://www.youtube.com/watch?v=-QO57RHzxus which was quite helpful for the first few steps.
Now, after posting this I just found this:
https://community.powerbi.com/t5/Desktop/How-to-expand-multiple-columns-to-new-rows-at-the-same-time...
Which seems to solve my problem!
Hi @SysLostInBI
Try this steps
let
Source = Json.Document(File.Contents("C:\Users\mrepczynski\OneDrive - Network Homes\Desktop\test.json")),
result = Source[result],
result1 = result{0},
data = result1[data],
Custom1 = Table.FromRecords( data ),
#"Transposed Table" = Table.Transpose(Custom1),
#"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each List.Combine( { [Column1], [Column2] } )),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
Custom = Table.FromRows( List.Zip( #"Removed Other Columns"[Custom] ) )
in
Custom
Hi @SysLostInBI
I think your json file describes 16 records: 2 dimensions x 2 timestamps x 2 values => 2 x 2 x 2 = 8 and that 2 times.
So the expanded result in Power Query is:
If your json file looks as following you get the expected result.
{
"result": [
{
"metricId": "xx",
"data": [
{
"dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1585872000000, "values":241
},
{
"dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1586044800000, "values":1067
},
{
"dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1585872000000, "values":172
},
{
"dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1586044800000, "values":771
}
]
}
]
}
Regards FrankAT
Thanks, but no. The JSON is actually formed as I described it.
It originates from a commercial product and I have to deal with it 🙂
@SysLostInBI , refer if this can help
https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...
Many thanks. I actually watched the first video you are referencing just before posting this question 🙂 it does not contain the answer sadly.
I also watched https://www.youtube.com/watch?v=-QO57RHzxus which was quite helpful for the first few steps.
Now, after posting this I just found this:
https://community.powerbi.com/t5/Desktop/How-to-expand-multiple-columns-to-new-rows-at-the-same-time...
Which seems to solve my problem!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |