Power Automate - Compose tool failing on blank excel rows

Question

My compose statement is reading in excel table.

However when it gets to a blank row i get error "Action 'Compose_2' failed"

Is there anyway to bypass this error to deal with blank records? These compose 1 and 2s are done for 5 different date columns, hence 10 composes.

If any of these are empty cells, I want to have my compose not fail , I want it to output empty and I want it to continue to the next column.

compose 1 -

items('Apply_to_each')?[concat('Period Planned ', '/', ' Revised Date')]

Compose 2 -

addDays('1899-12-30',int(outputs('Compose')),'yyyy-MM-dd')

blank row throwing error:

full flow -

data coming in (online excel)-

output list -

So it looks like my 2nd record in the incoming data isnt going into my sp list - which is fine - just its throwing an error in my power automate flow... Ideally would be nice to copy it over regardless and not pass over it.

What is blank/null though? Is it the output from the first compose? Can you provide a screenshot? — Skin, May 02 '23 at 20:56
@Skin Included screenshot. It literally is a blank row/cell in the excel that is causing the error. — Jonnyboi, May 03 '23 at 12:37
You need to check if the value is blank/null before continuing. — Skin, May 04 '23 at 00:58

score 2 · Accepted Answer · answered May 05 '23 at 01:01

Just looking at your approach and using Compose steps where you have, there's nothing wrong with that but I'm ultimately missing context around the rest of the flow.

Because of that, I've ignored your loop component (because I can see you're doing that based on the expressions) and focussing on what you can do prior to your loop so you don't have to deal with blank records.

This is the basic table I am working with ...

You can clearly see that there is a single blank record that I have used to test with.

This is the basic flow I mocked up the answer with ...

You can see, I've taken the data from the Excel table and simply filtered on it so as to remove any records that are deemed as being blank.

The expression to the right hand side of the filter is simply ...

string('')

... and you can see I've chosen records that are not equal to.

Now, if you look at the data before and after ...

Before Filter ...

[
    {
        "@odata.etag": "",
        "ItemInternalId": "5353aa33-9512-458d-8bf7-99d59aa981a3",
        "Revised Date": "2023-02-20T00:00:00.000Z"
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "1538d945-7516-4618-b080-a391ea65d7a9",
        "Revised Date": "2023-02-20T00:00:00.000Z"
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "d44e2dbd-5606-44aa-b4d6-c7835f0a781c",
        "Revised Date": ""
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "91d471f6-3729-4d49-887a-0fb43c2cd37c",
        "Revised Date": "2023-02-20T00:00:00.000Z"
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "8117d5b9-e805-4d2f-a2eb-43085db70258",
        "Revised Date": "2023-02-20T00:00:00.000Z"
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "b37bbb39-47e2-403d-bedf-8440dc1ff699",
        "Revised Date": "2023-03-06T00:00:00.000Z"
    },
    {
        "@odata.etag": "",
        "ItemInternalId": "2944a98d-71a4-4a63-8bd9-356386c9a03b",
        "Revised Date": "2023-03-13T00:00:00.000Z"
    }
]

After Filter ...

{
    "body": [
        {
            "@odata.etag": "",
            "ItemInternalId": "5353aa33-9512-458d-8bf7-99d59aa981a3",
            "Revised Date": "2023-02-20T00:00:00.000Z"
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "1538d945-7516-4618-b080-a391ea65d7a9",
            "Revised Date": "2023-02-20T00:00:00.000Z"
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "91d471f6-3729-4d49-887a-0fb43c2cd37c",
            "Revised Date": "2023-02-20T00:00:00.000Z"
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "8117d5b9-e805-4d2f-a2eb-43085db70258",
            "Revised Date": "2023-02-20T00:00:00.000Z"
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "b37bbb39-47e2-403d-bedf-8440dc1ff699",
            "Revised Date": "2023-03-06T00:00:00.000Z"
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "2944a98d-71a4-4a63-8bd9-356386c9a03b",
            "Revised Date": "2023-03-13T00:00:00.000Z"
        }
    ]
}

... the blank record has been removed.

From here, your loop should refer to the output of the Filter array step and not the output from the List rows present in a table step as that has now been superseded by the cleaner set of data.

But each row may have 5 columns but only column 1 is an empty cell(in excel) , will this work with the other 4 columns and not skip those? — Jonnyboi, May 05 '23 at 13:25
I think you need to update your question and be a lot clearer with your requirement. If there are additional columns that you’re checking for, you need to be explicit with your question. Provide examples for data coming in and data going out. — Skin, May 05 '23 at 13:27
Well, not entirely. If you’re doing the same thing over each column then just have a filter step before processing each column that removes the blanks. It’s not clear how you’re processing the data but from what you’ve said, it sounds like you’re processing each cell, not each row. So just replicate the filter step each time and change the column you’re filtering on to be the next date column from the last. Make sense? — Skin, May 05 '23 at 21:00
I th ink so.. but I still want to include the row values that have empty cells in excel in my sharepoint list. Not skip those rows. — Jonnyboi, May 08 '23 at 13:20

Power Automate - Compose tool failing on blank excel rows

1 Answers1

Before Filter ...

After Filter ...