Forum Discussion
Move Column Items to New Column and Value
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))