cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ez1138
Helper V
Helper V

Filter by Column Name, Field Entry

Hi, I have a table that I'm attempting to filter and in-turn, create an HTML table and populate an email. I have many, many columns with many of them contain the word "status" within the name of the column.  Let's call them ABC_status, DEF_status.  Ideally I would like to display entries under the columns with "status" for "Good", "Bad" or "Doesn't Matter".

 

Good Ones

  • ABC

Bad Ones

  • DEF

Doesn't Matter

  • None

What is the best way to accomplish this?

13 REPLIES 13
Mira_Ghaly
Dual Super User
Dual Super User

@ez1138 

You need to use an Odata filter on your Get Items

 

ABC_status eq 'Good' or ABC_status eq 'Bad' or ABC_status eq 'Doesn't Matter' or DEF_status eq 'Good' or DEF_status eq 'Bad' or DEF_status eq 'Doesn't Matter'

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
ez1138
Helper V
Helper V

Since I have 40-50 columns with "status" in them, do you have to put in each one?

Mira_Ghaly
Dual Super User
Dual Super User

@ez1138 

Yes unfortunately which is too much , alternatively you can make a calculated column on sharepoint and use this column in your powerautomate flow.

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Appreciate the replies.  I use SQL as a back-end (Azure SQL DB).  How would I go about doing that?  Also, if I choose to create that long string in the flow, I'm assuming that I place that in the filter query area, correct?

asd2525
Helper II
Helper II

I would make a new Excel table that just has the column names listed in the rows. You could have "Good" "Bad" and "Doesn't matter" and organize them accordingly. Then you could use the dynamic content from that table in your ODATA filter.

ez1138
Helper V
Helper V

Sounds interesting with the Excel.  I would not know how to implemented that or what exactly it should look like but I will give that a try too.

ez1138
Helper V
Helper V

Can you provide an example of what the excel table should look like?

Scratch that. After going through it I'm pretty sure I misunderstood your question. Could you screen shot an example of the data you are pulling and an example of how you want it to end up? I think this is actually something that could be done easier with Power Query but I would need to see the data set up to know.

ez1138
Helper V
Helper V

Audit_IDCompany_IDABC_StatusDEF_StatusFGH_StatusIJK_StatusLMN_Status
12GoodGoodBadBadBad

 

Ideal Report

 

Good

  • ABC
  • DEF

Bad

  • FGH
  • IJK
  • LMN

But you will have more than one row right? Or are you just wanting the most current row?

ez1138
Helper V
Helper V

Just one row at a time. I run the flow from Audit_ID.

Mira_Ghaly
Dual Super User
Dual Super User

@ez1138

You can try as well using filter array 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

K I would use power query to pull your table in. Transpose it so columns are rows. Filter out anything that doesn't contain status. Load the query into a pivot table. Put the answers column (the one that says good, bad) in the rows field first then put the column with the column name in the row field. This will end with grouping the columns that contain the word status by good or bad (or if there are any other possible responses) if there are responses you don't care about you can filter those out as well. 

I know this isn't a power automate answer but I believe it is the best solution for your process. If you need help setting up the power query let me know. I'd be happy to help. Though I have a cold right now and am on the verge of falling asleep so it probably wouldn't be till tomorrow at the earliest. 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (2,936)