Excel Power Query - Filter rows by comparing two columns

Question

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

Let we have a table

Just click filter button and choose some kind of comparation

Insert some value, for example 1

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

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 28, 2019 at 21:52

Sergey Lossev

1,54011 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2019-10-28 00:40:56Z

4

Add a custom column:

Filter on True
Then delete the custom column

answered Oct 28, 2019 at 0:40

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

Richard · Accepted Answer · 2019-10-28 04:54:42Z

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"

Collectives™ on Stack Overflow

Excel Power Query - Filter rows by comparing two columns

3 Answers 3

Let we have a table

Just click filter button and choose some kind of comparation

Insert some value, for example 1

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

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Let we have a table

Just click filter button and choose some kind of comparation

Insert some value, for example 1

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

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related