Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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   
Need Help
Show and Tell
Tutorial Requests
  • KNP's avatar
    KNP
    4 years ago

    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 

     

12 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        KNP
        Super User
        4 years ago

        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.