Category: Filter

Getting Specific Files And IDs In SharePoint Using Power Automate

I encountered an issue when trying to filter a a file by filename, that was in a SharePoint document library.

When needing to get a specific SharePoint file ID. It can be troublesome to filter the Files. For example Using a ‘Get files’ action we can see that the properties of the file are encased inside {} meaning that SharePoint is using some calculation on the Document Library to create these fields.

Contents

This post will go over a common problem I have seen using SharePoint action to get a specific file ID.
There are three parts to this post:
The Problem
The Solution
Conclusion

The Problem?

I encountered an issue when trying to filter a a file by filename, that was in a SharePoint document library.

When needing to get a specific SharePoint file ID. It can be troublesome to filter the Files. For example Using a ‘Get files‘ action we can see that the properties of the file are encased inside {} meaning that SharePoint is using some calculation on the Document Library to create these fields.

Attempting to use a filter query on any of the fields encased between {} returns an error. For example I am trying to filter the file called ‘Readthis.txt‘ I get a ‘Bad Request’ Error:

The error message I receive is:

Column ‘Name’ does not exist. It may have been deleted by another user.
clientRequestId: 75462fg8-686d-48p1-5se7-e8l97jk84025
serviceRequestId: 75462fg8-686d-48p1-5se7-e8l97jk84025

I have read online that using Title as the column name is correct, although I do not get an error, the output is empty.

The Solution

Now the best part, the solution!

The way I have been using to filter out ‘Get files‘ action is by using the ‘Filter array‘ action. This action will allow us to filter the calculated columns that SharePoint is using, like {Name}, {Link}, {FilenameWithExtension}, Etc.

To get setup, we want the ‘Get files‘ action to pull in ALL files, so we don’t want to have any filter at this stage.

Now add a ‘Filter array‘ action, put the dynamic content value in the From field. On the left side select the dynamic content from the ‘Get files‘ action. The right side put what you want to filter on.
So for example, I want to filter and get the file ‘Readthis.txt‘. So my ‘Filter array‘ action looks like this:

Now when running the Flow, the ‘Filter array’ action is properly filtering out the filename:

Conclusion

I wrote this blog post based on a scenario I have helped solved on the Power Automate Community Forum.

Hopefully someone else find this information useful.

Thanks for reading!

Power Automate Expression Union – Return Unique Values

Power Automate Expressions – union
Using union to remove duplicates on two different collection arrays

This blog will be looking at an expression called union() This expression can be used to remove duplicates from 2 collections. In this example, I will be using an Excel file to demonstrate this. But this can be done with any data

The Scenario

I will be taking an Excel file that has many Paper Items.

We want to remove all the duplicate items from the Plastic Item column. Why would we want to do this?
One reason may be that we get this file from a 3rd party, but we only care about the unique items to import into another system (CRM, CDS/Dynamics, SharePoint, Etc.)

Steps

First we want to add an Initialize variable, Type = Array
Next, we need to get the data we want to use. In this example I am using List rows present in a table

Now we add a Apply to each loop, and use the dynamic content value from our Excel action
Inside the Loop we add a Append to array variable action, and add the column we want to remove duplicates from. In this example Paperitem

We are done inside the loop..

Outside the loop we add a Compose action, this is where we will put the expression union
In the Expression tab type union()
Select the dynamic content tab and pass the array variable to union twice
(Click your variable comma click variable again) Your expression should look something like this:

union(variables('paperItemArray'),variables('paperItemArray'))

Done!!

Final Thoughts

Now we have a Array of all unique Plastic Items, which can be imported into another system based on your needs.

Thanks for reading!


Excel Filtering on Columns With a Space

Using the Filter or the Select Query in Excel is very easy to use. However the issues come up when there is a space in the column you are trying to Query, which results in a Bad Request error.

The Problem

Using the Filter or the Select Query can be limited on the Excel connector. The issues come up when there is a space in the column you are trying to Query, which results in a Bad Request error.

The Solution

A relatively easy fix would be to change the column name to have no spaces. Sometimes this is not viable or possible due to many systems talking to each other. Or perhaps a third party is supplying the Excel doc.

The fix in my example shows how to use the Select, and Filter array actions in Power Automate.
Select is used to select certain columns to output.
Filter Array is used to filter on certain conditions and values.

Step 1 – Add the Select action under the Excel List rows present in table action

The Map section is used by naming the column on the left, and selecting the column on the right

Step 2 – Add Filter array action under the Excel List rows present in table action

The Filter array action can be used for all types of Odata like filters

Conclusion

Some Actions have a limitation on the Odata filter and Select queries. Some examples include:

  • When filtering on Names with special characters
    • James O’ Henry
  • Columns with spaces

Thank you for reading.