Remove rows based on condition

I'm trying to remove rows based on a condition, whereby once it is found i then want to remove that row and then all 4 rows beneath it. I have considered using the multi-field formula tool and then create a flag which can then be filtered out. My issue is I don't know how to configure the multi-field formula tool.

Please see example data below. I want to remove all 3 rows under Total for Year as well as the Total for Year row.

Your help would be much appreciated

Name	Value
Week 1	10
Week 2	20
Week 3	30
Week 4	40
Week ....

Total for Year
Sales	500
Cost	400
Profit	100

Workflow

Preparation

Expression

Accepted answers

mceleavey

@FECL3 ,

I've attached a workflow that will now dynamically pull the data you need and create the following format:

This will handle any number of tables. If you're dynamically loading them in though, it sounds like you could do this before smooshing the data together.

Anyway,

I hope this helps,

mceleavey_solution.yxmd

All comments

Jonathan-Sherman

Hi @FECL3,

Could you not just use the StartsWith() function?

StartsWith([Name], 'Week')

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

20211213 - FilterOutRows.yxmd

FECL3

No, the above is a sample data so that won't work as there are duplicates fields. A better example of the data format would be this:

	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5


Total for Month 1
Sales	40
Cost	20
Profit	20

In effect the table at the bottom is a summary of the detailed table above, but I need to remove it and it's difficult because the naming convention is the same as the above. The only unique identifier is the header, which comes out similar to the "Total for Month"

Thanks

Jonathan-Sherman

Hi @FECL3,

In that case it could be achieved with a multi-row formula and a filter tool:

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

20211213 - FilterForFirstTable.yxmd

FECL3

Thank you. Although this solution won't work if there are more tables that I need below. I need the solution to be more precise.

I am dynamically inputting in multiple files into one workstream so there is more data below the table.

Thanks again

mceleavey

Hi @FECL3 ,

@Jonathan-Sherman 's solution is correct with the data you and scenario you've provided. If you want anything else, you need to provide a representative dataset to cover these scenarios.

Can you please provide a dataset that is representative and covers the issues you're having?

FECL3

Hi @mceleavey

Please see dataset below. Please bare in mind that there can be multiple iterations of this as I am dynamically

Thanks

	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5


Total for Month 1 - Product 2
Sales	40
Cost	20
Profit	20


	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5


Total for Month 1 - Product 1
Sales	40
Cost	20
Profit	20

mceleavey

@FECL3 ,

...and, what do you want to see? That data is a different layout to the previous request.

mceleavey

@FECL3 ,

I've attached a workflow that will now dynamically pull the data you need and create the following format:

This will handle any number of tables. If you're dynamically loading them in though, it sounds like you could do this before smooshing the data together.

Anyway,

I hope this helps,

mceleavey_solution.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703