Custom Object to Data table

[Fredd]

I would need some help to get list of values in my custom object into a data table

I have my custom object %SessionDetails"

My idea was to use For Each loop to put the values into a data table, but I get this error message: Variable 'SessionDetails' must be a list, datatable or datarow.

Any ide how to get all rows into a data table?

[Agnius · Accepted solution]

Custom objects are not iterable in Power Automate Desktop, so you cannot really do it this way. The only way to do what you want would be to use Convert custom object to JSON and then use Parse text with regular expressions to retrieve the values from the JSON string into a list. 

Using \d{4}(-\d{2}){2}T(\d{2}:){2}\d{2}\+\d{2}:\d{2} as the regex should match the dates. If you disable the "First occurrence only" toggle in Parse text, it will return them all into a list. You could then use a For each loop on the matches and use %CurrentItem% to get the value from the object as %Object[CurrentItem]%.

------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Fredd]

Thanks! 

Perhaps this give a better understanding. As I get a JSON that I store as a CustomObject. Should I do something else with the json to get it into a data table?

{
    "id": "b125e62f-3cf0-4a9c-a841-a56107426902",
    "started": "2024-01-14T14:17:28.063",
    "energy": 21.476,
    "energies": {
        "2024-01-14T14:17:28+00:00": 0,
        "2024-01-14T22:00:00+00:00": 2.3739999999997963,
        "2024-01-14T23:00:00+00:00": 3.4030000000002474,
        "2024-01-15T00:00:00+00:00": 3.4179999999996653,
        "2024-01-15T01:00:00+00:00": 3.438000000000102,
        "2024-01-15T02:00:00+00:00": 3.2760000000002947,
        "2024-01-15T03:00:00+00:00": 3.4329999999999927,
        "2024-01-15T04:00:00+00:00": 1.625,
        "2024-01-15T05:00:00+00:00": 0.4130000000000109,
        "2024-01-16T05:34:07+00:00": 0.09600000000000364
    },
    "ended": "2024-01-16T05:34:07.233"
}

[Agnius]

The issue with your JSON is that the "energies" object is not an array of objects, but a single object with several named properties that have their values. The dates there are names for the values, and not values of their own. If your object looked like this, you could iterate through it:

{
    "id": "b125e62f-3cf0-4a9c-a841-a56107426902",
    "started": "2024-01-14T14:17:28.063",
    "energy": 21.476,
    "energies": [
        {
            "Date":"2024-01-14T14:17:28+00:00",
            "Value": 0
        },
        {
            "Date":"2024-01-14T22:00:00+00:00",
            "Value": 2.3739999999997963
        },
        {
            "Date":"2024-01-14T23:00:00+00:00",
            "Value": 3.4030000000002474
        },
        {
            "Date":"2024-01-15T00:00:00+00:00",
            "Value": 3.4179999999996653
        },
        {
            "Date":"2024-01-15T01:00:00+00:00",
            "Value": 3.438000000000102
        },
        {
            "Date":"2024-01-15T02:00:00+00:00",
            "Value": 3.2760000000002947
        },
        {
            "Date":"2024-01-15T03:00:00+00:00",
            "Value": 3.4329999999999927
        },
        {
            "Date":"2024-01-15T04:00:00+00:00",
            "Value": 1.625
        },
        {
            "Date":"2024-01-15T05:00:00+00:00",
            "Value": 0.4130000000000109
        },
        {
            "Date":"2024-01-16T05:34:07+00:00",
            "Value": 0.09600000000000364
        }
    ],
    "ended": "2024-01-16T05:34:07.233"
}

This is what an array of objects looks like in JSON. And that would be iterable. But what you have is different.

As such, use the approach I suggested earlier - use Parse text with regular expressions enabled and the regex I suggested. Like this:

(this assumes %JSON% is where your JSON blob is stored - use appropriate variable naming for what is already in your flow here).

The important things here are:

1. Paste the regex pattern I suggested in the previous reply into the "Text to find" field
2. Enable the "Is regular expression" toggle
3. Disable the "First occurrence only" toggle to return all matches

This will result in all of those dates being returned in a list in %Matches%.

You can then build a loop that iterates through them, like a For each loop. You would store the current date into %CurrentItem% within the loop. And you can then get the value from your object (after you convert the JSON to a custom object) as %Object['energies'][CurrentItem]%.

You can either add those to a separate list, or insert them to a table, or whatever you want to do with it.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Fredd]

I did tried your solution, and it may be the best way to solve it. And it worked great! Thanks!