Search code examples
jsonpower-automate

Get column headers dynamically from json in Power Automate

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.

enter image description here

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

Solution

  • 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.

    BUT XML Element names cannot start with a digit and as a result it will fetch the names encoded. Time for Plan B.

    Plan B

    Rename your xpath step to Obj2Array, and then update it to:

    json(
  replace(
  replace(
  replace(
  replace(
    concat('[', string( body('Parse_JSON_Hours') ), ']' ),
  ':', '"value":'),
  ',', '},{"name":'),
  '""', '","'),
  '[{', '[{"name":')
)

    This will result in an array of this format:

    [
  { "name": "12/12", "value": 9 },
  { "name": "12/13", "value": 9 },
  { ... }
]

    Then check your Apply to each 2 step is using outputs('Obj2Array').

    Finally, in your loop, you can use:

    item()?['name']

    and

    item()?['value']