Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
munk
Helper I
Helper I

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 
     
NameRate NameRate
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   
2 ACCEPTED SOLUTIONS
davehus
Memorable Member
Memorable Member

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"

View solution in original post

11 REPLIES 11
davehus
Memorable Member
Memorable Member

Amazing thank you 🙂

davehus
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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. 🙂

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

davehus
Memorable Member
Memorable Member

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"

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
Memorable Member
Memorable Member

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. 

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 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.