Column of Content Missing from Otherwise Successful Create Excel File Flow

[Phineas]

I have a created a flow that isolates Status 'ACTIVE' AND 'CML DIGITAL' in a SP List and creates those items in an Excel Workbook in a SP Library.

The flow is working, however one column 'Title' in the SP List is not populating to the corresponding column 'CML_ID' in the Excel Workbook, even though all other data is populating.

Also, the flow seems to run longer than it takes to actually populate the workbook with the data. The source SP List have 1200+ items, only 130 or so match the parameter of 'ACTIVE' or 'CML DIGITAL'. Why does the flow continue to run, even with a terminate action after the 'Apply to each'?

Excel files in SP Library -

Files contents - 

 

The SP List Status Column - 

The SP CML_ID (Title) Column - 

Raw Output of Flow SP List Get Items. You can see 'Title' in the array.

The 'CML_ID' expression is - item()?['Title'] - but as you can see no data is populating this column
in the Excel workbook. What am I missing. 

[creativeopinion]

@Phineas Since you are looping through the outputs in your Filter Array action—can you verify the output of your Filter Array action? Confirm the dynamic content key for the Title column. Theoretically—it shouldn't change, however... if the value isn't displaying then the key might be incorrect.

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Phineas]

How do I verify the output of my filter array?

[creativeopinion]

@Phineas To verify the output of the Filter Array action, take a look at your last flow run. Expand the Filter Array action and review the Outputs. 

I noticed that you are using the length() function after the Filter Array action. Im assuming you are checking the length of the Filter Array action. If so, I would recommend that you add a Condition action after. Place the output of the Compose action storing the length of items returned in the first value field, change the operator to is not equal to and enter 0 into the second operator field. 

Place the rest of your flow actions into the Yes branch. This way, if no items are returned from your Filter Array action, your flow will stop running. 

Can you explain the need for your Filter Array action? It looks like the query is the exact same query you've used in the Get Items action—unless I'm missing something.

To learn more about how to use the Filter Array action, please refer to this YT Tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[Phineas]

To the question about the filter array, I built it first then modified to add the Get items and, you are correct, they have the same filter.

If I remove the filter array what does that change, will the flow still work?

If I remove the filter array what becomes of the Compose2 and the Condition I just added.

Below is the modified flow and the raw output from the filter array showing the 'Title' column, which is the one not populating in the Excel.

Here is the error message for the Get items, that might be why the data is not appearing in the one column in Excel -
The API 'sharepointonline' returned an invalid response for workflow operation 'Get_items' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems' is missing required property 'body/value/49/Title'.'

After a run the filter array has the following -

Updated flow -

Here is the raw output of the filter array -

[creativeopinion · Accepted solution]

@Phineas If you remove the filter array action—you'll have to adjust the rest of your actions that reference the Filter Array output. You'll need to use the dynamic content from the Get Items action. I seems like the Filter Array action is redundant in your case. It might be a good idea to remove it. 

I would recommend adjusting the Compose action that is returning the length of items from the Filter Array action, to return the length of items from the Get Items action. This way you can verify if any items have been returned from your Get Items action before continuing on with your flow. 

In you error it seems like some items in the your list are missing a title. You might want to verify your data first before you continue to build your flow. 

[Phineas]

The data, including the 'Title' contents, are present in the Filter Array.

The 'Add a row into table' action 'CML_ID' parameter has "", when all other fields have their correct content.

Somewhere, for some reason, between the Filter Array and the Add a row action the 'Title/CML_ID' data is getting lost, while all the other data comes through fine. What am I missing?

[creativeopinion]

@Phineas Could you try to insert a Compose action above the Add a Row into a Table action to hold the Current item? This Compose action will output the current item being looped through. You can verify if the Title is there.

[Phineas]

Not sure what I did, but I finally got it.