PowerQuery Advanced Filtering Help

[munk]

Hello,

I am aware you can filter by all blanks, or for specific data-points, HOWEVER, I am trying to filter between a mix of blank/existing data. On the table below, you can see a mix of names that have either:
- A rate for each entry

- No rates for each entry

What I want to filter out, are entries that have a mix of both existing rates and blanks, so I can correct them instead of just leaving them halfway completed.

What I have			What I want
Name	Rate		Name	Rate
MarkFoods	$5.00		OscarBing	$5.00
MarkFoods	$5.00		OscarBing
MarkFoods	$5.00		OscarBing	$5.00
TonyCars			OscarBing
TonyCars			OscarBing	$5.00
TonyCars			ChristinaLarge
TonyCars			ChristinaLarge	$5.00
OscarBing	$5.00
OscarBing
OscarBing	$5.00
OscarBing
OscarBing	$5.00
ChristinaLarge
ChristinaLarge	$5.00

[davehus · Accepted solution]

Hi Munk, Here is the PQ code to copy into excel, just add data blank query and paste below. You'll need to map the source to your source table in excel.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0synbLz08pVtJRMlWK1SFOJCQ/r9I5sQgkQBbfvzg5scgpMy8dbiKyCPlKnDOKMotLMvMSfRKL0lNh6jBEgYpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Null Rate", each if [Rate] = null then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Null Rate", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Rate Value", each if [Rate] <> null then 1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Rate Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each Table.Group(#"Changed Type3", {"Name"}, {{"Null Rate Sum", each List.Sum([Null Rate]), type nullable number}, {"Value Rate Sum", each List.Sum([Rate Value]), type nullable number}})),
Custom = #"Added Custom"{0}[Custom],
#"Changed Type2" = Table.TransformColumnTypes(Custom,{{"Name", type text}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, #"Changed Type2", {"Name"}, "Changed Type2", JoinKind.LeftOuter),
#"Expanded Changed Type2" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type2", {"Null Rate Sum", "Value Rate Sum"}, {"Null Rate Sum", "Value Rate Sum"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Changed Type2", "Filter", each if [Null Rate Sum]>0 and [Value Rate Sum]>0 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Null Rate Sum", "Value Rate Sum"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Filter] = 1))
in
#"Filtered Rows"

[davehus]

Hi @munk ,

Heres a file. 🙂 https://docs.google.com/spreadsheets/d/1oNw37FU1-6Nyy4FIQhzRtCHDKxBcw_Ox/edit?usp=sharing&ouid=10615...

[davehus · Accepted solution]

Hi @munk ,

File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=10615...

[munk]

Amazing thank you 🙂

[davehus]

You're welcome. Good job you sent it to me, you would have had a bit of pain applying the M code on your finisehed solution. Any problems in the future, drop me a mail. 🙂 

[davehus]

Hi @munk ,

Please see PBIX file https://drive.google.com/file/d/1wJc6CAKgCoAU6c1YDZ8vLMOk6lnKu9kd/view?usp=sharing

This should help you with your issue. If it does, please accept my answer. 🙂

[munk]

Is there a chance you could send me the excel version?

[davehus · Accepted solution]

Hi Munk, Here is the PQ code to copy into excel, just add data blank query and paste below. You'll need to map the source to your source table in excel.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0synbLz08pVtJRMlWK1SFOJCQ/r9I5sQgkQBbfvzg5scgpMy8dbiKyCPlKnDOKMotLMvMSfRKL0lNh6jBEgYpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Null Rate", each if [Rate] = null then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Null Rate", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Rate Value", each if [Rate] <> null then 1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Rate Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each Table.Group(#"Changed Type3", {"Name"}, {{"Null Rate Sum", each List.Sum([Null Rate]), type nullable number}, {"Value Rate Sum", each List.Sum([Rate Value]), type nullable number}})),
Custom = #"Added Custom"{0}[Custom],
#"Changed Type2" = Table.TransformColumnTypes(Custom,{{"Name", type text}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, #"Changed Type2", {"Name"}, "Changed Type2", JoinKind.LeftOuter),
#"Expanded Changed Type2" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type2", {"Null Rate Sum", "Value Rate Sum"}, {"Null Rate Sum", "Value Rate Sum"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Changed Type2", "Filter", each if [Null Rate Sum]>0 and [Value Rate Sum]>0 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Null Rate Sum", "Value Rate Sum"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Filter] = 1))
in
#"Filtered Rows"

[munk]

For context, I would like the source to be

= Excel.CurrentWorkbook(){[Name="Foods_Billing_Report"]}[Content]

but keeping the rules you put in place with the original source. How can I do this?

@davehus 

[davehus]

Hi @munk, Could you send me an excel copy of your file with one or 2 lines of dummy data in it. The thing is with PQ is the code is explicit, so if names don't match in the column headers it will start giving errors. 

[munk]

https://docs.google.com/spreadsheets/d/1_fWz43ikm71xJsdnHBN0JPIyk2VXrKF_/edit?usp=sharing&ouid=11570...

The only difference here, is the columns from the example are now known as

Name = DBName

Rate = RatingGroup

the rest of the data is irrelavent to the problem

@davehus 

[munk]

Do you know how I can move this fix to my main data set? I am running into a problem on step 1 of the source (sorry I am very new to PQ). How do I make it pull data from my workbook, while keeping these rules of "let _t" etc.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0synbLz08pVtJRMlWK1SFOJCQ/r9I5sQgkQBbfvzg5scgpMy8dbiKyCPlKnDOKMotLMvMSfRKL0lNh6jBEgYpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Rate = _t]),

It is just pulling the data from the example I sent, and not allowing me to work with the actual data.