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 II
Dual Super User II

@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 II
Dual Super User II

@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 I
Helper I

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 II
Dual Super User II

@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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,414)