How to Remove Filtered Rows in Power Query

[earthworm]

I have a data of 1.2 Million. and there are many steps I need to perform in power query based on multiple criteria in multiple columns.
What i want to do is to remove filtered rows based on my requirement for each column using filter so that once all my data is removed i can then close and load to / or create connection.
I need to shape data just like we do in excel by removing all the unnecessary rows and once the data is read I need to create connection only since data length is huge.

How can i remove data based on multiple criteria in single column and multiple criteria in multiple columns just like we do it in excel . This time data is huge and i need to do it in power query

 

[jdellasala]

This is a suggestion based on the really nice solution by @alansidman. I'm just adding an option to have a variable as you asked.
Create a Named Range called FilterBy:

Book1
	G	H
1	Fliter By:	FAIL
2
3	All Values
4	1
5	2
6	3
7	4
8	5
9	6
10	D
11	DS
12	F
13	FAIL
14	G
15	H
16	0
Sheet1

Cell Formulas
Range	Formula
G4:G16	G4	=SORT(UNIQUE(VSTACK(Table1[A],Table1[B],Table1[C],Table1[D],Table1[E])))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G25	Expression	=NOT(ISBLANK($G4))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=$G$4#

It doesn't show, but cell H4 is also the Named Range FilterBy.
I (clumsily) set that up so that any value in the table can be selected for FilterBy. The conditional formatting is just to see what the resulting list looks like. Now you can bring that single cell (no table required) into Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="FilterBy"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Column1 = #"Changed Type"{0}[Column1]
in
    Column1

The Change Type step is needed, otherwise numeric values break the main query!
Now, just a tiny tweak to @alansidman's code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}, "en-US"),{"A", "B", "C", "D", "E"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each if Text.Contains([Merged],FilterBy ) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom"})
in
    #"Removed Columns"

I'm sure it could be done with multiple filters as well, but that would require a table, and someone like @alansidman to suggest the code!