Forum Discussion

CMC's avatar
CMC
Helper I
3 years ago
Solved

Move Column Items to New Column and Value

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

 

 

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    guess your data is Json format text

    try this code

    = Table.FromRecords(Json.Document(YourText))

     

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    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

     

    • CMC's avatar
      CMC
      Helper I
      3 years ago

      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
      },

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        guess your data is Json format text

        try this code

        = Table.FromRecords(Json.Document(YourText))