topic Re: PowerQuery Advanced Filtering Help in Power Query

PowerQuery Advanced Filtering Help

Anonymous — Wed, 23 Mar 2022 19:06:37 GMT

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

Re: PowerQuery Advanced Filtering Help

KNP — Wed, 23 Mar 2022 20:07:42 GMT

Ok, so here's what I have in time available right now 😊.

This could be made much more elegant but may give you a starting point.

(paste this into the advanced editor of a blank query)

let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W8k0synbLz08pVtJRUjHVMzBQitUhTTQkP6/SObEIJKhAroB/cXJikVNmXjqKycii1FDnnFGUWVySmZfok1iUngpXjCEM1RELAA==", 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, {{"Name", type text}, {"Rate", Currency.Type}} ), #"Grouped Rows" = Table.Group( #"Changed Type", {"Name"}, {{"all", each _, type table [Name = nullable text, Rate = nullable number]}} ), #"Added Custom" = Table.AddColumn( #"Grouped Rows", "Custom", each Table.AddColumn([all], "index", (all) => if all[Rate] = null then 1 else 2) ), #"Aggregated Custom" = Table.AggregateTableColumn( #"Added Custom", "Custom", {{"index", List.Distinct, "Count of index"}} ), #"Extracted Values" = Table.TransformColumns( #"Aggregated Custom", {"Count of index", each Text.Combine(List.Transform(_, Text.From), ","), type text} ), #"Filtered Rows" = Table.SelectRows( #"Extracted Values", each ([Count of index] = "1,2" or [Count of index] = "2,1") ), #"Expanded all" = Table.ExpandTableColumn(#"Filtered Rows", "all", {"Rate"}, {"Rate"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded all", {"Name", "Rate"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Rate", Currency.Type}}) in #"Changed Type1"

Re: PowerQuery Advanced Filtering Help

KNP — Wed, 23 Mar 2022 20:12:02 GMT

Added PBIX file.

Re: PowerQuery Advanced Filtering Help

Anonymous — Wed, 23 Mar 2022 20:39:57 GMT

Not quite sure how to interpret this or if it would work with the existing steps in the workbook (it was handed to me after previous work has been done on other columns).

I do not really have any experience with the advanced editor, so I am not really sure what I am looking at here.

Re: PowerQuery Advanced Filtering Help

KNP — Wed, 23 Mar 2022 21:15:02 GMT

Did you see the PBIX file I attached?

Open that and go to Transform Data, look at the individual steps and see if they make sense to you.

Let me know if you have any questions on the specific steps and I'll try and answer them.

Re: PowerQuery Advanced Filtering Help

Anonymous — Wed, 23 Mar 2022 21:18:27 GMT

I'm unable to open it, as I am using PQ based in Excel, instead of the PowerBI program

Re: PowerQuery Advanced Filtering Help

KNP — Wed, 23 Mar 2022 21:23:16 GMT

Attached Excel version.

Re: PowerQuery Advanced Filtering Help

Anonymous — Thu, 24 Mar 2022 16:21:59 GMT

When moving this fix to the bigger dataset, how do I change the source while keeping the "let t" ?

Re: PowerQuery Advanced Filtering Help

KNP — Thu, 24 Mar 2022 16:25:46 GMT

You don't need that when you change the source.

Keep the #"Grouped Rows" down. Make sure to point the #"Grouped Rows" step to your previous step, then modify the grouping as required.

Re: PowerQuery Advanced Filtering Help

Anonymous — Thu, 24 Mar 2022 17:20:30 GMT

let
Source = Excel.Workbook(File.Contents("C:\Users\riesc_86uyy47\Desktop\Files\Full Daily Report.xlsx"), null, true),
FM_Billing_Report_Sheet = Source{[Item="FM_Billing_Report",Kind="Sheet"]}[Data]
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Name"},
{{"all", each _, type table [Name = nullable text, Rate = nullable number]}}
),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"Custom",
each Table.AddColumn([all], "index", (all) => if all[Rate] = null then 1 else 2)
),
#"Aggregated Custom" = Table.AggregateTableColumn(
#"Added Custom",
"Custom",
{{"index", List.Distinct, "Count of index"}}
),
#"Extracted Values" = Table.TransformColumns(
#"Aggregated Custom",
{"Count of index", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
#"Filtered Rows" = Table.SelectRows(
#"Extracted Values",
each ([Count of index] = "1,2" or [Count of index] = "2,1")
),
#"Expanded all" = Table.ExpandTableColumn(#"Filtered Rows", "all", {"Rate"}, {"Rate"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded all", {"Name", "Rate"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Rate", Currency.Type}})
in
#"Changed Type1"
in
FM_Billing_Report_Sheet

Receiving a "Token Comma expected" error on the bolded line

Re: PowerQuery Advanced Filtering Help

KNP — Thu, 24 Mar 2022 17:47:17 GMT

You're missing a comma at the end of your FM_Billing_Report_Sheet statement, refencing the wrong step for the Grouped Rows and needed to lose the 'in FM_Billing_Report_Sheet' at the end.

Try this, may still need some alterations but this should get you closer.

let Source = Excel.Workbook( File.Contents("C:\Users\riesc_86uyy47\Desktop\Files\Full Daily Report.xlsx"), null, true ), FM_Billing_Report_Sheet = Source{[Item = "FM_Billing_Report", Kind = "Sheet"]}[Data], #"Grouped Rows" = Table.Group( FM_Billing_Report_Sheet, {"Name"}, {{"all", each _, type table [Name = nullable text, Rate = nullable number]}} ), #"Added Custom" = Table.AddColumn( #"Grouped Rows", "Custom", each Table.AddColumn([all], "index", (all) => if all[Rate] = null then 1 else 2) ), #"Aggregated Custom" = Table.AggregateTableColumn( #"Added Custom", "Custom", {{"index", List.Distinct, "Count of index"}} ), #"Extracted Values" = Table.TransformColumns( #"Aggregated Custom", {"Count of index", each Text.Combine(List.Transform(_, Text.From), ","), type text} ), #"Filtered Rows" = Table.SelectRows( #"Extracted Values", each ([Count of index] = "1,2" or [Count of index] = "2,1") ), #"Expanded all" = Table.ExpandTableColumn(#"Filtered Rows", "all", {"Rate"}, {"Rate"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded all", {"Name", "Rate"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Rate", Currency.Type}}) in #"Changed Type1"

This website can be very helpful for formatting and pointing out errors in code.

https://www.powerqueryformatter.com/formatter

Re: PowerQuery Advanced Filtering Help

Anonymous — Thu, 24 Mar 2022 17:53:56 GMT

Definitely closer 🙂 I changed Name and Rate to the more accurate column headers, but still getting error below.

#"Grouped Rows" = Table.Group(
#"Changed Type",
{"DBName"},
{{"all", each _, type table [DBName = nullable text, RatingGroup = nullable number]}}

"Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly."

Re: PowerQuery Advanced Filtering Help

KNP — Thu, 24 Mar 2022 18:01:06 GMT

You should have just been able to replace the entire code in the advanced editor...

In your workbook, follow the gif below.