Excel Power Query - Filter rows by comparing two columns

0
votes

I have a table in Excel that is self outer joined in Power Query Editor to create pairs

Table named Items
-----------------
Item   Value
A      1
B      2
C      3

I create a From Table/Range query that is closed and loaded to connection only that is named "Items"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

Then another query is made that is a reflexive cross join

let
    Source = Items,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Items),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item", "Value"}, {"Custom.Item", "Custom.Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Item", "Item.2"}, {"Custom.Value", "Value.2"}, {"Value", "Value.1"}, {"Item", "Item.1"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Value", each [Value.1]*[Value.2])
in
    #"Added Custom1"

At this point the query shows 9 rows (3x3)

Item.1 Value.1 Item.2 Value.2 Value
A      1       A      1       1
A      1       B      2       2
A      1       C      3       3
B      2       A      1       2
B      2       B      2       4
B      2       C      3       6
C      3       A      1       3
C      3       B      2       6
C      3       C      3       9

I am struggling to edit the query so it will filter the results down to the cases of value.1 <= value.2

Item.1 Value.1 Item.2 Value.2 Value
A      1       A      1       1
A      1       B      2       2
A      1       C      3       3
B      2       B      2       4
B      2       C      3       6
C      3       C      3       9

The Power Query Editor UI does not seem to offer a x < y filter capability and I am not sure how to use the Power Query M formula language to filter the data the way I want.

Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with Analysis Services, Excel, and Power BI workbooks. A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.

3
excelpowerquerym

3 Answers

3
votes

Add a custom column:

enter image description here

  • Filter on True
  • Then delete the custom column

enter image description here

2
votes

Let we have a table

enter image description here

Just click filter button and choose some kind of comparation

enter image description here

Insert some value, for example 1

enter image description here

Then change that value to column name you want to compare to

enter image description here

enter image description here

let
    Source = #table({"col1", "col2"}, {{99,88}, {11,22}, {33,44}}),
    filter = Table.SelectRows(Source, each [col1] <= [col2])
in
    filter
0
votes

I ended up editing the query using Advanced Editor and used step #"Added Custom1" table in Table.SelectRows for next step.

let
    Source = Items,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Items),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item", "Value"}, {"Custom.Item", "Custom.Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Item", "Item.2"}, {"Custom.Value", "Value.2"}, {"Value", "Value.1"}, {"Item", "Item.1"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Value", each [Value.1]*[Value.2]),

added this

    #"Selected Rows" = Table.SelectRows(#"Added Custom1", each [Value.1] <= [Value.2]),
    #"Sorted Rows" = Table.Sort(#"Selected Rows",{{"Value", Order.Ascending}, {"Value.1", Order.Ascending}, {"Value.2", Order.Ascending}})
in
    #"Sorted Rows"