Category: Excel

Power Automate – Excel Dynamic Filename

In the Excel connector we are forced to use the file picker (folder icon) to select a Excel file. But what if we did not know the filename, or we are expecting NEW Excel files.. We will need a dynamic filename.

I have learnt this method from John Liu. I have pointed so many people, and community users to John’s blog when they run into this Excel connector limitation. I have found multiple people thinking its too complicated, or confusing, because the method works with Graph API to get the File ID.
So this blog will be a very easy guide to follow and hopefully will be able to get your Flow being able to use a dynamic filename. If you have any questions or problems with this guide or other Power Automate related questions, reach out to me on Twitter.

New Update – Easy Way 2021

The following video explains a way easier way to do this, no API, no messy File ID’s. Check it out!

Things to Know

To make this even easier, we will be having the Excel file that be stored into a SharePoint Document Library. We can also use the SharePoint HTTP call action to better build our REST call more easily.
* We will still need the Excel file to have a table defined *

Getting Started

First we will create a new SharePoint Document Library for the files, or use an existing document library.

Next, we want to have one Excel file in the Library to start, so we can make sure this is working. Here is what mine looks like:

My example, I have a Document Library called ‘Excel Files’ with a Excel file to start with

Overview of The Flow

For the Flow, I am using the SharePoint action ‘Send an HTTP request to SharePoint‘. This action uses MS Graph in the background, but makes it a lot easier to construct a API call.
First we will use a simple call to SharePoint using the ‘Send an HTTP request to SharePoint‘ action to get all the Document Libraries for our site.
Than use the output of that action to find the Document Library Drive ID.
This is how we navigate through the SharePoint site, we want to do this until we are in the folder of our Excel File.

Lets Build that Flow

First we add ‘Send an HTTP request to SharePoint‘ action

Uses Graph API in the back-end

In the SharePoint HTTP action, use the following:
– Site Address: If your SharePoint site does not show up in the drop down, click use ‘Enter custom value’ and type the homepage of your site
– Method: GET
– Uri: _api/v2.0/drives/
– Headers: accept application/json

Now trigger the Flow to get the Output of the HTTP request.

You can also read and get what you need through the Output Body window

I am using Visual Studio Code to paste the Output into, its free, and easy to use.
If you have lots of Document Libraries, you may want to use a Find feature and search for the Document Library name. For this demo I will be searching Excel Files.
We need to look for a specific ‘id’ inside the output

Copy this ‘id‘,we can use this value to navigate inside that Document Library in our HTTP request. Our new URI should looks something like:
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8

Now we need to add /root:/ to the end of our URI

We are almost complete! We now need to specify the path to our file. Here are some examples:

If file is directly in the Document Library(No Folders) – Just add the filename with the extension
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/MyNewExcelFile.xlsx

If file is in a folder – Add the folder name, than the Excel file with the extension
* NOTE: If folder name has spaces, than use %20 instead of a space *
Folder name = Historical Files
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/Historical%20Files/MyOldExcelFile.xlsx

Mine is not in a folder so I will be using the first one. Now to make the file dynamic, I will be using a Compose action to store the Filename. So in my HTTP Request I will be using my Compose action with the Filename and extension at the end. My example will look like this:

The Filename, can come from any Dynamic content, I am just using a Compose for the sake of this Demo

Now test the Flow to validate the HTTP request is valid. The output should have some references to the file, like the name of the file, size, created date time, and last modified date time. My output looks like this:

Next, to get the file ID, we use a Compose action with an expression.
Add a Compose action, inside the Compose we will use the HTTP request Body dynamic content in a expression:

With the fx logo present on the dynamic content tab. Click the Dynamic content Body. Now we use JSON path to grab the file ID.
This is done by adding a ?['id'] to the end of the expression. My expression looks like this:

body(‘Send_an_HTTP_request_to_SharePoint’)?[‘id’]

body('Send_an_HTTP_request_to_SharePoint')?['id']
You can copy my expression, just make sure to change the Send_an_HTTP_request_to_SharePoint to your HTTP request action name

Next add an Excel Get tables action. Use the outputs from the Compose – get file ID for the File

Now add an Excel List rows present in table action.
File: Use the outputs from Compose – get file ID action (same as we did for Get Tables)
Table: Click Enter custom value. Use this expression to get the first table name
first(body('Get_tables')?['value'])?['id']
Power Automate has been updated the new expression to get the tables is:
first(outputs('Get_tables')?['body/value'])['id']
** Same as before. Get_tables is my action name **

first(outputs(‘Get_tables’)?[‘body/value’])[‘id’]

Success!!

Now, sometimes the Dynamic content of the Excel columns wont be there now. You can easily grab any column you like using the item() expression. For example I have a column named Tasks so my expression will be
item()?['Tasks']

item() can only be used inside an Apply to each

Conclusion

Being able to use a Dynamic filename in the Excel connector can be tricky. But I am hoping with this guide, anyone who needs this business requirement can easily follow this, and get it done.

Again I wanted to give a shout out to John Liu. I originally learnt this method from his blog, which can be found here:
http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

Thanks for reading. If you have any questions or problems with this guide or other Power Automate related questions, reach out to me on Twitter.

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.