Filter column based on another column

[nleuck]

Hello All,

I need some help to find out if it's possible to filter a column based on a column in the Power Query Editor in Power BI Desktop. I have a column called OrderQty and I need it to be less than (<) than other column called TotalAvailable. It seems when I'm in Query Editor though I can only use values and not other columns as a filter. Is there a work around this? Any help would be greatly appreciated!

Thanks,

[nleuck]

I ended up just doing the work in the Text of my query where I get the data source. It ended up being easier that way.

@HotChilli your idea would have worked if the two columns were in the same table when I pulled the data in. Thanks for your response.

@v-xuding-msft  your idea would have worked as well except for once you merge queries the data because Mixed and I need it to be Direct Query, that way I don't have to manaul refresh the data. Thanks for your response.

[v-xuding-msft]

Hi @nleuck ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nleuck]

@v-xuding-msft 

No. None of these helped. Thanks anyways.

[HotChilli]

Can you provide a data sample, please?

It's not clear to us where the columns you are asking about are located.

Different tables or the same table?

I'm sure we can help.

[nleuck]

@HotChilli 

The columns are in two different tables.

[HotChilli]

OK, thanks.  Is there a data sample?

[v-xuding-msft]

Hi @nleuck ,

Is there a related column between the two tables？If there is, you can merge queries firstly and then to use the function of if to add a new column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICEwZKsTrRSkZAprEBiDAG842BTFNTIGEGkTcBKQXxTc3BfFMYH2hIbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Table (2)", {"ID"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Column1"}, {"Table (2).Column1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom", each if[#"Table (2).Column1"] <= [Column1] then [Column1] else null)
in
    #"Added Custom"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

If you are comparing values in the same row, add a column  and give it a value (e.g. 1) if one column is bigger than the other (and 0 if not).

Then you filter out the 1s or 0's (depending on your logic) using the dropdown from the column header