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 trying to figure out how to move the below data into custom rows and values. the data comes in this format:
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Road",
"time": "2022-12-02T00:53:29.000+00:00",
"text": "Energy consumption of Aeotec Switch is 12.46",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 12.46,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "12.46",
"epoch": 1669942409374,
"hash": 3653307885
Try to move those into the following format
Solved! Go to Solution.
guess your data is Json format text
try this code
= Table.FromRecords(Json.Document(YourText))
Table.PromoteHeaders(Table.Transpose(#table(2,List.Transform(List.RemoveItems(Text.SplitAny(YourText,"#(lf),"),{"#(cr)"}),each Splitter.SplitTextByDelimiter(": ")(_)))))
Only for the sample data you provided, it may need to be adjusted when applied to the actual data
thanks @wdx223_Daniel I should have posted the complete data set, its not working for me, raw format below.
[
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-02T00:53:29.000+00:00",
"text": "Energy consumption of Aeotec Switch is 12.46",
"component": "main",
"componentLabel": "main",
"capability": "energyMeter",
"attribute": "energy",
"value": 12.46,
"unit": "kWh",
"data": {},
"translatedAttributeName": "Energy consumption",
"translatedAttributeValue": "12.46",
"epoch": 1669942409374,
"hash": 3653307885
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-02T00:53:25.000+00:00",
"text": "Power meter of Aeotec Switch is: 3992.473W",
"component": "main",
"componentLabel": "main",
"capability": "powerMeter",
"attribute": "power",
"value": 3992.473,
"unit": "W",
"data": {},
"translatedAttributeName": "Power meter",
"translatedAttributeValue": "3992.473",
"epoch": 1669942405561,
"hash": 1405394063
},
{
"deviceId": "cd489ed6-111a-46c7-bd31-ce1a91d7efcd",
"deviceName": "Aeotec Switch",
"locationId": "31cb9eb0-74fd-4471-a51b-37e1ec3f969b",
"locationName": "Home",
"time": "2022-12-02T00:53:25.000+00:00",
"text": "Power meter of Aeotec Switch is: 3996.395W",
"component": "main",
"componentLabel": "main",
"capability": "powerMeter",
"attribute": "power",
"value": 3996.395,
"unit": "W",
"data": {},
"translatedAttributeName": "Power meter",
"translatedAttributeValue": "3996.395",
"epoch": 1669942405266,
"hash": 2849180396
},
guess your data is Json format text
try this code
= Table.FromRecords(Json.Document(YourText))
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.