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

     

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"