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

 

[alansidman]

Sample data showing a before and after will help tremendously. show your data using XL2BB. No pictures as we cannot manipulate data in a picture.

 

[earthworm]

Sample data showing a before and after will help tremendously. show your data using XL2BB. No pictures as we cannot manipulate data in a picture.

Sample Data

Book1
	A	B	C	D	E
1	A	B	C	D	E
2	1	D	3	F	FAIL
3	2	F	3	3	3
4	3	G	3		4
5	4	G	3	2	5
6	5	H	3	DS	FAIL
7	6		FAIL	FAIL	FAIL
8	G	H	FAIL	FAIL
9	FAIL	FAIL	FAIL	FAIL	D
10	FAIL	3	FAIL	FAIL	FAIL
11	G	3	FAIL	FAIL	3
Sheet1

Result

Book1
	A	B	C	D	E
14	A	B	C	D	E
15	2	F	3	3	FS
16	3	G	3		FS
17	4	G	3	2
Sheet1

Please note that FAIL is just mentioned as a sample . The Fail term can be dynamic based on human judgement which i will manage manually depending on my choice . The whole row will be deleted based on my filter criteria.

 

[alansidman]

I don't understand your filter criteria. How does the FS come into play? Please explain your thought process so that we can better understand.

 

[earthworm]

I don't understand your filter criteria. How does the FS come into play? Please explain your thought process so that we can better understand.

FS can be any value its just a demo . My main focus is FAIL . Anything that contains FAIL in any column i will remove that entire row in power query.

 

[Alex Blakenburg]

Alan may have a better way but see if this works for you.
It relies on merging the columns with a "|" separator.
Upper casing it
Adding a "|" to the front
Filtering out any rows with the "|FAIL" in the merged column field.

Currently assumes the starting point is a Table called "Table1" (I prefer to have meaningful table names)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddColumnMergeCols = Table.AddColumn(Source, "MergedColumn", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)),Text.From),"|")),
    #"Uppercased Text" = Table.TransformColumns(AddColumnMergeCols,{{"MergedColumn", Text.Upper, type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Uppercased Text", {{"MergedColumn", each "|" & _, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Added Prefix", each not Text.Contains([MergedColumn], "|FAIL")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MergedColumn"})
in
    #"Removed Columns"

 

[earthworm]

Alan may have a better way but see if this works for you.
It relies on merging the columns with a "|" separator.
Upper casing it
Adding a "|" to the front
Filtering out any rows with the "|FAIL" in the merged column field.

Currently assumes the starting point is a Table called "Table1" (I prefer to have meaningful table names)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddColumnMergeCols = Table.AddColumn(Source, "MergedColumn", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)),Text.From),"|")),
    #"Uppercased Text" = Table.TransformColumns(AddColumnMergeCols,{{"MergedColumn", Text.Upper, type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Uppercased Text", {{"MergedColumn", each "|" & _, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Added Prefix", each not Text.Contains([MergedColumn], "|FAIL")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MergedColumn"})
in
    #"Removed Columns"

Is there any another approach ? I need to perform in real time based on filter values that can be fail or any other depending on my choice.

 

[Alex Blakenburg]

You might need to give us more details on a realistic scenario. It sounds like the scenario in your post#3 is too simplistic.
The code I gave you works for that scenario.

 

[alansidman]

Similar approach that Alex took.

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],"FAIL") 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"

 

[Alex Blakenburg]

@alansidman, it took quite a while to find a working version of merging columns based on the columns being dynamic. If you don't already have M code tucked away to do that you might want to take a look at my 2nd line.