Filter by Column Name, Field Entry

[ez1138]

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?

[Mira_Ghaly]

@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'

[ez1138]

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

[Mira_Ghaly]

@ez1138 

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

[ez1138]

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]

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]

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]

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

[asd2525]

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]

Audit_ID	Company_ID	ABC_Status	DEF_Status	FGH_Status	IJK_Status	LMN_Status
1	2	Good	Good	Bad	Bad	Bad

Ideal Report

Good

• ABC
• DEF

Bad

• FGH
• IJK
• LMN

[asd2525]

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

[ez1138]

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

[Mira_Ghaly]

@ez1138

You can try as well using filter array 

[asd2525]

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.