Forum Discussion
PowerQuery Advanced Filtering Help
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 |
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"Hi Anonymous ,
File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=106154846235665801323&rtpof=true&sd=true
11 Replies
Hi Anonymous ,
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. 🙂
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"
Hi Anonymous ,
File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=106154846235665801323&rtpof=true&sd=true