PowerQuery Advanced Filtering Help

[Anonymous]

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

[KNP]

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"

[Anonymous]

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.

[KNP]

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.

[Anonymous]

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

[KNP]

Attached Excel version. 

[Anonymous]

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

[KNP]

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.

[Anonymous]

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

[KNP · Accepted solution]

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 

[Anonymous]

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

[KNP]

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

In your workbook, follow the gif below.

[KNP]

Added PBIX file.