Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 |
Solved! Go to 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"
Hi @munk ,
File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=10615...
Hi @munk ,
File here. 🙂 Let me know if all is ok. https://docs.google.com/spreadsheets/d/1PVQjiE6UnT5mWJioHenXtBmuHAKs_9MW/edit?usp=sharing&ouid=10615...
Amazing thank you 🙂
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. 🙂
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?
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?
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.
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |