cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Suggestion needs for building a power automate desktop flow.

Hi all.

I have an excel data from an excel worksheet. It contain 11 rows and 16 columns. (see some pictures below)

Atlantis1_0-1701601461450.png

Atlantis1_1-1701601667108.png

My flow will ask a user to enter T1001 for example and with that value i need to find and display all the information belongs to T1001 (even T1001 (2)) columns in that excel data which matches what the user has entered. Basically the user only enters any T number within that data table and my job is to find and display all the information belongs that match the user´s entered T number.

 

This is what my flow looks like at the moment.

Atlantis1_2-1701602449664.png

the problem is that it only shows T1001 and not T1001 (2). This is what i would like the result to be.

Atlantis1_3-1701602704817.png

I´d appreciate any help and thankful if explanation is easy to understand since i´m newbie. Thanks again.

 

 

 

6 REPLIES 6

Use find and replace activity

enable regular expression option then give the pattern as  T1001.*

UshaJyothi20_0-1701675193907.png

then give these column names to retrieve coloumn into list activity 

 

Hope this helps

Usha 

for more clarification please share sample excel file so that i can give a try

 

VBA would make faster work of this.

 

Get input from user
For each column, if row 1 value does not contain input, delete column.

 

Advantage:  Your team could use this directly in Excel, and they wouldn't need to use PAD.

 

Further functionality.  You could just create a single macro (no input needed) that divides the workbook up into all variations (1 workbook per user) and saves out each workbook for them to access.

 

I don't know what happens after this phase of your flow, but if each one was already separated, it would be really easy for the bot to grab it, then you don't need the bot to do anything but:

Open workbook
Run Macro
'then do whatever else you were going to do with the data

Good luck!

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services at www.peakdigitran.com/appointments and you can find me on LinkedIn at https://www.linkedin.com/in/michael-annis-80903/,

Hi MichaelAnnis.

Thanks for your suggestions. I never thought VBA can be a solution. My problem is that i´m not neither as good as you in vba or pad. But i will try to digest your suggestion and let see how far i can go. 

 

Hi Usha.

Thanks for your time looking at my request. You suggest enable regular expression function and i´d be grateful if you can create a flow so i can try and learn some new feature in pad.

Please take a look at my shortcome flow:

SET FilePath TO $'''C:\\Users\\DD\\Desktop\\PAD\\WM interface per maskin\\W interface per maskin_test.xlsx'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePath Visible: True ReadOnly: True Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Display.InputDialog Title: $'''VERKTYGS KOMPENSERING FÖR SVARVSTÅL''' Message: $'''Skriv SvarvStål börjar me T''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> UserInput ButtonPressed=> ButtonPressed
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3', 'Column4'], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''], [$'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
LOOP FOREACH CurrentItem IN ExcelData.Columns
IF Contains(CurrentItem, UserInput, False) THEN
DISABLE Variables.RetrieveDataTableColumnIntoList DataTable: CurrentItem ColumnNameOrIndex: UserInput ColumnAsList=> ColumnAsList
Display.ShowMessageDialog.ShowMessage Message: CurrentItem Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
END
END

and this is the excel file.

 

 

 

Agnius
Most Valuable Professional
Most Valuable Professional

Use the Find or replace in data table action and make it return all matches, as well as not try to match the full cell contents.

 

This will return a data table with column and row indexes for each match found in the table. Each match will result in a row in the matches table. You can then use those to return all data from your original data table.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

 

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

-------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards, Agnius Bartninkas

Hi Agnius

I tried your suggestion but no matter what i have like all matches in Find or replace in data table action it still has this:

Atlantis1_0-1701805570343.png

and when i open DataTableMatches it is empty.

Atlantis1_2-1701805822523.png

This is my exceldata which contains 11 rows 16 columns with first row is header.

Atlantis1_1-1701805706044.png

This is my selection in Find and replace action

Atlantis1_3-1701805977279.png

 

Thanks.

 

Agnius
Most Valuable Professional
Most Valuable Professional

It doesn't find anything. And that's because the value you are looking for is not within the values of the table, but are in fact in the header row.

 

When that is the case, you don't need to find it. You can reference a value like this: %ExcelData[0][UserInput]%. The 0 here indicates row 1. Depending on which row you want, you can pass a different index (or use a loop to get all of them). You can also use %UserInput% as the name of the column in Retrieve data table column into list to retrieve the entire column into a list, and have a list of all the items related to this one code from user input.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

-------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards, Agnius Bartninkas

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,480)