Get column headers dynamically from json in Power Automate

Tom
January 18, 2024
162 views
0 votes
2 Answers

I have a json which looks like:

[
  {
    "TimePeriod": "12/12/23 - 12/26/23",
    "ResourceName": "rob brien",
    "TimesheetStatus": "Submitted",
    "SubmittedBy": "rob brien",
    "LastModified": "12/12/23 7:12 AM",
    "InvestmentTasks": [
      {
        "InvestmentID": "PRO13796",
        "Investment": "Credit Risk Regulatory ",
        "Description": "A3-Dev/Build",
        "Hours": {
          "12/12": 9,
          "12/13": 9,
          "12/14": 9,
          "12/15": 9,
          "12/16": 9,
          "12/17": 0,
          "12/18": 9,
          "12/19": 9,
          "12/20": 9,
          "12/21": 0,
          "12/22": 9,
          "12/23": 9,
          "12/24": 9,
          "12/25": 9,
          "12/26": 9,
          "Total": 99
        }
      }
    ]
  }
]

Now, I’m manually mapping the headers in excel in "add a row into table" step (in Power Automate).

Now the issue arrives with dates which are under "Hours" object.

Dates may change either it can increase or decrease or it can be from a different month like 11/1,11/2,11/26 and can get any no of days from a month.

How can I dynamically get these date column headers?

I have come across an article using xpath function:https://mytrial365.com/2023/07/13/discovering-json-value-using-a-dynamic-key-in-power-automate/#:~:text=Discovering%20JSON%20Value%20Using%20a%20Dynamic%20Key%20in%20Power%20Automate,-Kailash%20Ramachandran%20Power&text=Power%20Automate%20offers%20several%20options,outputs%20for%20the%20parsed%20properties.

Xpath Step :xpath(xml(body(‘Parse_JSON_Hours’)), ‘/Hours/*’)

APPLY TO EACH 2 STEP: passing the output of xpath

EACH ITEM: xpath(item(), ‘name(/*)’)

Error: Unable to process template language expressions in action ‘xpath’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘xml’ parameter is not valid. The provided value cannot be converted to XML: ‘JSON root object has multiple properties. The root object must have a single property in order to create a valid XML document. Consider specifying a DeserializeRootElementName. Path ‘outputs.body.Sat_12_2

I need to get dates header dynamically as no of days are not fixed or it can be for a different month .
Expected output:

12/12   12/13
9       9    

   so on...

Let me know what I’m doing wrong

Tags: json power-automate

Answers

I think the easiest way is for you to get what you need is to use the Advanced Data Operations connector and the Json Properties to Name Value Pair Array operation.

https://statesolutions.com.au/json-properties-to-name-value-pair-array/

It will produce this output which you can then loop through and do what you need with …

[
  {
    "propertyName": "InvestmentTasks",
    "propertyType": "Array",
    "propertyValue": [
      [
        {
          "propertyName": "Description",
          "propertyType": "String",
          "propertyValue": "A3-Dev/Build"
        },
        {
          "propertyName": "Hours",
          "propertyType": "Object",
          "propertyValue": [
            {
              "propertyName": "12/12",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/13",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/14",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/15",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/16",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/17",
              "propertyType": "Integer",
              "propertyValue": 0
            },
            {
              "propertyName": "12/18",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/19",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/20",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/21",
              "propertyType": "Integer",
              "propertyValue": 0
            },
            {
              "propertyName": "12/22",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/23",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/24",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/25",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "12/26",
              "propertyType": "Integer",
              "propertyValue": 9
            },
            {
              "propertyName": "Total",
              "propertyType": "Integer",
              "propertyValue": 99
            }
          ]
        },
        {
          "propertyName": "Investment",
          "propertyType": "String",
          "propertyValue": "Credit Risk Regulatory "
        },
        {
          "propertyName": "InvestmentID",
          "propertyType": "String",
          "propertyValue": "PRO13796"
        }
      ]
    ]
  },
  {
    "propertyName": "LastModified",
    "propertyType": "String",
    "propertyValue": "12/12/23 7:12 AM"
  },
  {
    "propertyName": "ResourceName",
    "propertyType": "String",
    "propertyValue": "rob brien"
  },
  {
    "propertyName": "SubmittedBy",
    "propertyType": "String",
    "propertyValue": "rob brien"
  },
  {
    "propertyName": "TimePeriod",
    "propertyType": "String",
    "propertyValue": "12/12/23 - 12/26/23"
  },
  {
    "propertyName": "TimesheetStatus",
    "propertyType": "String",
    "propertyValue": "Submitted"
  }
]

… the only caveat is that it takes an object, not an array so you can do it for the first row in your dataset and go from there.

For your data set, using the XML approach may be a little troublesome, if I convert your JSON to XML, I get this …

<root>
    <InvestmentTasks>
        <Description>A3-Dev/Build</Description>
        <Hours>
            <_x0031_2_x002F_12>9</_x0031_2_x002F_12>
            <_x0031_2_x002F_13>9</_x0031_2_x002F_13>
            <_x0031_2_x002F_14>9</_x0031_2_x002F_14>
            <_x0031_2_x002F_15>9</_x0031_2_x002F_15>
            <_x0031_2_x002F_16>9</_x0031_2_x002F_16>
            <_x0031_2_x002F_17>0</_x0031_2_x002F_17>
            <_x0031_2_x002F_18>9</_x0031_2_x002F_18>
            <_x0031_2_x002F_19>9</_x0031_2_x002F_19>
            <_x0031_2_x002F_20>9</_x0031_2_x002F_20>
            <_x0031_2_x002F_21>0</_x0031_2_x002F_21>
            <_x0031_2_x002F_22>9</_x0031_2_x002F_22>
            <_x0031_2_x002F_23>9</_x0031_2_x002F_23>
            <_x0031_2_x002F_24>9</_x0031_2_x002F_24>
            <_x0031_2_x002F_25>9</_x0031_2_x002F_25>
            <_x0031_2_x002F_26>9</_x0031_2_x002F_26>
            <Total>99</Total>
        </Hours>
        <Investment>Credit Risk Regulatory</Investment>
        <InvestmentID>PRO13796</InvestmentID>
    </InvestmentTasks>
    <LastModified>12/12/23 7:12 AM</LastModified>
    <ResourceName>rob brien</ResourceName>
    <SubmittedBy>rob brien</SubmittedBy>
    <TimePeriod>12/12/23 - 12/26/23</TimePeriod>
    <TimesheetStatus>Submitted</TimesheetStatus>
</root>

… and as you can see, the slashes don’t really translate too well.

To answer your question though, when converting to XML, you’ll need to use this expression …

xml(json(concat('{ "root": ', variables('Data'), ' }')))

… obviously replacing the variables('Data') part with your own JSON object.

- SamNseir
- January 18, 2024 at 2:38 am
- 0 votes
0
You were close. An XML document needs a root element. And your Parse JSON Hours is returning:
```
{
  "12/12": 9,
  ...
  "Total": 99
}
```
Which doesn’t have one root property.

Update your xpath step to:
```
xpath(
  xml(json(
    concat('{"Hours:', string(body('Parse_JSON_Hours')), '}')
  )),
  '/Hours/*'
)
```
And that should resolve the error.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.