cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
desert_deux
Frequent Visitor

Getting rows from Excel into SharePoint only if they fall within certain dates

Hi all🙂

I am very new to Power Automate and can't seem to find a solution for my issue. To provide some context, here is an example of the table in the Excel files I work with. There are a lot of items in the table, and I only need those wherein the specific date falls within the start and end date.

 

Nameother dataStart dateEnd dateSpecific date

 

Now, I know how to do this in Excel, but I don't want to have to copy and paste the formula every time. So, here is what I envision that a flow will be able to do: 

Once a new excel file lands in a designated document library, the flow will go through the excel table, identify those items in which the specific date falls between start and end date, and then list those items in a designated SharePoint list. Is this possible? 

 

I'd appreciate it if you guys could help me out!😊

 

14 REPLIES 14
MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux 

welcome to the community!

 

there are a couple of things you should check before we go on:

- You can define how a flow starts (e.g. Whenever a new file is created (or modified) within a SharePoint library), the flow always starts whenever there is a new document uploaded into the specific library. You should make sure that there is no other content uploaded to this library, otherwise your flow would trigger every time and run into an error or worse put wrong information into the SharePoint list. If there is multiple content uploaded, you should consider using Trigger conditions

- Make sure your Excel data is formatted as table (and uses the same name for the table) and your date is also formatted correctly, otherwise you need more actions within Power Automate (to make it more dynamically) and sometimes also get errors of incorrect date format. You need the ISO 8601 format

 

Let's check the flow, how it could look like:

MarvinBangert_0-1672743790148.png

Basic explanation: When a new file is created within a SharePoint library, list all rows present in the table of the file that was uploaded, then filter the information it gets from the Excel file, parse the content (to make it easier for later actions), then apply to each item we filtered and create a new SharePoint list item.

 

Now let's check every step:

MarvinBangert_1-1672744001184.png

In the trigger (first action), select the site and library you want to monitor whenever a new item is created (depending on your requirements, you could maybe also use "When a file is created or modified (properties only)").

 

MarvinBangert_2-1672744120675.png

To get the content from the Excel file that was just uploaded, select the location, document library and then select the file by using the dynamic content "File name with extension" from your trigger. If you always use the same Table name within Excel, you can make this static ("Tabelle1"), otherwise you would need to first get the table name from the Excel file and insert it as a dynamic value too.

Also make sure you select DateTime Format as "ISO 8601" (first expand "show advanced options"). This is were is is necessary, that you actually have a proper date format.

 

MarvinBangert_3-1672744414716.png

The filter array is a bit more complex to write the expression, I will first give you some tips how to make it easier, then provide the full complex expression solution. We need to compare the specific date with the start date and end date, you cannot do it right away, so you would also need to convert them into "ticks" (that is again why you need the ISO 8601 format). We cannot select the dynamic value columns from the Excel file (we could add another step "parse JSON" between the Excel action and Filter array action, but we can also access the information in the following way), so we write a short expression to access it:

Tip 1: When you are inside an array, you can use the expression "item()" to access the current object within the array. To access your column, you just use the following syntax:

item()?['<your column name>']

just replace <your column name> with your value, e.g.:

item()?['start date']

now add these values within the two fields unter the "from" field:

MarvinBangert_4-1672745199084.png

It should look like this:

MarvinBangert_6-1672745567223.png

Also remember to select the compare method (start date "is less than or equal to" specific date; end date "is greater than or equal to" specific date; this will check if the specific date is between the start date and the end date).

Tip 2: You could already run your flow, but you would not see the expected solution, because Power Automate cannot easily compare two dates with each other like this, so we would need to format it first into something we can compare. There is an expression called "ticks()", it returns the number of ticks (100 nanoseconds interval) since 1 January 0001 00:00:00 UT of a string timestamp. Just wrap "ticks()" around your "item()" like:

ticks(item()?['start date'])

Your Filter array will look like this:

MarvinBangert_7-1672746066032.png

Now you can switch to the "advanced mode" and copy the expression you see:

MarvinBangert_8-1672746104311.png

Just save it somewhere locally like in notepad etc. Then do the same steps again with "end date" but use "greaterOrEquals" (or just replace the values within the expression you already created).


Afterwards we need to combine these expressions using an "and()" expression, this will also give you your final expression:

@and(
    lessOrEquals(
        ticks(item()['Start date']),
        ticks(item()['Specific date'])
    ),
    greaterOrEquals(
        ticks(item()['End date']),
        ticks(item()['Specific date'])
    )
)

Also make sure you remain the "@" in the beginning of the first expression. You can then add this expression within the advanced mode of your filter:

MarvinBangert_9-1672746351595.png

That was already the hardest part.

 

MarvinBangert_10-1672746381735.png

Next (if you like), you can add a "Parse JSON" action. This will format/parse the content, so you can use dynamic content later in your flow instead of writing expressions. The easiest way is to run the flow once before adding this action, select the output of the action you want to parse (in this case "Filter array") and then use the "Generate from sample" option and just paste in the output from the action. I just used this example:

  [
    {
      "Name": "Test",
      "other data": "Test",
      "Start date": "2023-01-01T00:00:00.000Z",
      "End date": "2023-01-08T00:00:00.000Z",
      "Specific date": "2023-01-05T00:00:00.000Z"
    },
    {
      "Name": "Test 3",
      "other data": "Test 3",
      "Start date": "2022-12-29T00:00:00.000Z",
      "End date": "2023-01-08T00:00:00.000Z",
      "Specific date": "2023-01-01T00:00:00.000Z"
    },
    {
      "Name": "Test 5",
      "other data": "Test 5",
      "Start date": "2023-01-02T00:00:00.000Z",
      "End date": "2023-02-01T00:00:00.000Z",
      "Specific date": "2023-01-12T00:00:00.000Z"
    },
    {
      "Name": "Test 6",
      "other data": "Test 6",
      "Start date": "2023-01-01T00:00:00.000Z",
      "End date": "2023-01-08T00:00:00.000Z",
      "Specific date": "2023-01-01T00:00:00.000Z"
    }
  ]

to receive this Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            },
            "other data": {
                "type": "string"
            },
            "Start date": {
                "type": "string"
            },
            "End date": {
                "type": "string"
            },
            "Specific date": {
                "type": "string"
            }
        },
        "required": [
            "Name",
            "other data",
            "Start date",
            "End date",
            "Specific date"
        ]
    }
}

The Content is the Body of your "Filter array" action as an dynamic value:

@{body('Filter_array')}

 

MarvinBangert_12-1672746817233.png

 

Last but not least, you need to create an item within the SharePoint list. Because we are running on an array, the "Apply to each" will be created automatically, when you add a dynamic content from the "Parse JSON" action. Otherwise you can do it manually and add the "Apply to each" action first and select the dynamic value body of "Parse JSON". Afterwards add the SharePoint "Create item" action within the "Apply to each".

Select the Site and List from the dropdown (I just used some random List here, therefore the columns don't match) and afterwards add the dynamic content from the "Parse JSON" action into the fields.

MarvinBangert_13-1672747063226.png


That's it, now you have a flow that triggers each time a new Excel-file is uploaded, checks if the specific date is between the start date and end date and writes these information into a SharePoint list.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

Hi Marvin,

 

thank you so much for your answer, it's a great help! I am a little confused about the filter array function though. Do I have to run two parallel filter arrays, one for Start Date is less than or equal to Specific date and the other one for the end date? Because it won't let me write two in one.

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux 

you need to switch into the advance mode of the filter array. In the basic mode you are only allowed to add one filter, within the advanced mode you can combine multiple filter by using the "and()" expression. Please select this to switch:

MarvinBangert_0-1672753541944.png

Afterwards you can add the solution I provided, or you can build it as I described it with the tips how to do it by yourself.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

Hi Marvin,

ok, I edited it in advanced mode and it seems to have worked. I tried running the flow but it's stuck on the second step and says it can't find the table, even though I formatted the content as a table and labeled it the same way I did while setting up the flow. Any ideas why that's not working? 

 

 

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux 

Please review the name of the table and the input into your flow. There is probably some issue that the table is not found.

 

Otherwise, you can also add the "Get tables" Excel action, you can again make the file input field dynamic (as the file input field within "list rows present in a table").

MarvinBangert_0-1672847939410.png

Please note, that when you add the "Id" value within the "list rows present in a table", it will create an "apply to each", because "get tables" can return 1 or more tables from Excel. So please make sure there is only one table within your Excel file. If you want to prevent that the "Apply to each" is created, you can use the following expression, which will always select the first table that is in the output of the "Get tables":

outputs('Get_tables')?['body/value'][0]?['id']

Change 'Get_tables' if you renamed your Excel action "Get Tables". The [0] defines on which position within the array that is returned the value is we are looking for. Because an array always starts with position 0. Then your flow will look like this:

MarvinBangert_1-1672848434130.png

 

Of course, you can also use the "Get tables" function to just see what the name of the table is and don't use is anywhere else in the flow.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

Hi @MarvinBangert ,

that output function solved my problem, thank you so much!

Now, the filter array is not working.🙈 This is the way I put the code into the advanced edit mode:

@and(
lessOrEquals(
ticks(item()['Startdatum']),
ticks(item()['Stichtag'])
),
greaterOrEquals(
ticks(item()['Enddatum']),
ticks(item()['Stichtag'])
)
)

 

I get the following error message:

Error Filter Array.PNG

Can you help me identify the problem? Thank you so much🙈

 

Best Regards,

desert_doux

@MarvinBangert , I may not have explained it clearly in my original post, but every item on my Excel list has a different start date, end date, and specific date. That means that the flow should check if the specific date for every item falls within the start and end date of every item. Is that still possible?

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux 

yes, it's still possible. Using the "item()" action runs on every object/item within the array. Using the [''] after item() you can define on which column to run.

The error already gives you a hint, in this case "Startdatum" doesn't exist within the table:

MarvinBangert_0-1672914918678.png

Please review your flow, if you are running on the correct table and that the columns are available you want to run on.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel
Join my User Group (German & English): Power Platform UG Cologne

desert_deux
Frequent Visitor

Hi @MarvinBangert ,

I fixed the date format in the Excel sheet and that seemed to fix it. Now I'm getting the following error message:

Error Filter Array.PNG

Would the problem be inside the Excel file itself? For example, the start date on one of the items is 20220502. 

Thank you so much for your help!

Best Regards,

desert_deux

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux,

yes, might be a problem with the format of the date, but in this case its more likely an empty value. Can you check within your Excel-File if "Stichtag" has some empty values? You would need to catch this e.g. adding an "if()" expression first checking if a column is empty. Otherwise maybe the column is still not recognized properly, can you please also check the outcome of "List rows present in a table", if there is your column included?

 

Here is a description about formatting and date values from Excel in Power Automate:
It can also be a problem already within the Excel-File, if you formatted the column as date field, it depends how you entered the values to actually work with them properly.

Example:

MarvinBangert_0-1672996096881.png

Here you can see two table columns with dates, both columns are formatted as date with the following setting:

MarvinBangert_1-1672996166012.png

On the left side, I entered the date as "01.01.23" (DD.MM.YY), on the left side I entered them as "2023-01-01" (YYYY-MM-DD). Based on the input in Excel, the right column can be formatted as I change it within the "format cells" settings, the left column input is not recognized as a date and wouldn't change the format, e.g.

MarvinBangert_2-1672997157928.png
MarvinBangert_3-1672997170845.png

This will also be visible within Power Automate:

Output of "List rows present in a table":

MarvinBangert_4-1672997301456.png
(Note: even though that my input in Excel is "YYYY-MM-DD", it's just about what is recognized as an actual date input in Excel, in my case also "01/01/2023" would work. I guess it also is about the installed Office language/version how input is desired so maybe on a German Office/Windows it might work different, but I am not that much into Office and for reference I am using only the English version of Office on an English Windows; I don't have another instance were I can reference how it would be with like a German Office/Windows. To test if it is actually recognized as a date, you can just change the cell formatting in Excel)

If we now use the ticks function, we get this:

01.01.2023 works fine, because you can just switch around 01 as day and month even as wrong formatted:

MarvinBangert_6-1672997396666.png

01.02.2023 already occurs some wrong calculations, because it's not clear from German or English date format, is it 1. February or 2. January. In this case 01.02.2023 is translated as 2. January and we get different ticks outcome.

MarvinBangert_7-1672997462019.png

We see this when we have something like "15.01.2023" where it can actually occurs errors as there is no 1. of the 15th month:

MarvinBangert_8-1672997636712.png

Also you might noticed based on the entry that I made within Excel, the date is actually saved in ISO 8601 format, no matter how I change the formatting in Excel to display my values.

 

Here is how you can solve it:

You can add an expression "formatDateTime()" and "parseDateTime()" around the "item()" expression to format it correctly.

Example:

 

 

formatDateTime(parseDateTime(item()?['Stichtag'], 'en-GB', 'dd.MM.yy'), 'yyyy-MM-dd', 'en-US')

 

 

In this case you can also try out the latest feature "format data by examples", if you are not that familiar with writing expressions:

After selecting the column you would like to format, you can enter some example values from your file and write the desired output, afterwards press "get expression" to let Power Automate write the expression for you as you need it. You can already test it with some other values right within the window:

MarvinBangert_9-1672998310732.png

What happens here: First the text is parsed as an actual date (this would already be enough to use the ticks() expression), and afterwards because we also defined a specific format, it's also formatted as "2023-01-01" (YYYY-MM-DD).

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel
Join my User Group (German & English): Power Platform UG Cologne

Hi @MarvinBangert ,

thank you! I managed to format all dates correctly, double checked that there are no empty values in the date columns, and now the dates seem to be shown correctly in the Outputs from the List rows present in a table:

Error_Time.PNG

Yet I still get the error saying "The template language function 'ticks' expects its parameter to be a string that represents a timestamp. The provided value is of type 'Null' ". I really don't know why that's showing up.😔

 

Best Regards,

desert_deux

 

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Strange, could you please send me a screenshot of the "Filter array" action within your flow?

Like this:

MarvinBangert_0-1673039541360.png

Please also copy in the filter expression you are using and put it in here.

Thanks in advance!

 


Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel
Join my User Group (German & English): Power Platform UG Cologne

Hello @MarvinBangert ,

here is what the filter array portion of my flow looks like:

FilterArray1.PNGFilterArray2.PNG

would the formatting be causing the issue?

 

Best Regards,

desert_deux

MarvinBangert
Most Valuable Professional
Most Valuable Professional

Hi @desert_deux 

Sorry for the delay, I was very busy last week!

I checked everything that you send me, but I cannot really find the issue here. Did you managed to find a solution? If yes, please share it.

 

Otherwise, for troubleshooting I would use the "Compose" action and try to go through the values, where the error could be, so do something like this:

MarvinBangert_0-1673858424964.png

Add the "Compose" action and add the "Stichtag" value (or any other column from your file). Because we are running on an array, the "Apply to each" will be created automatically. Afterwards go into the next line and add the expression you can see within the notes

ticks(<your excel dynamic value of the column>)

 

You can build parallel branches for each column you would like to check. This will run on each item and on each column you specify and you will see if the error occurs on only one item or all.

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel
Join my User Group (German & English): Power Platform UG Cologne

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (3,427)