Forum Discussion

nleuck's avatar
nleuck
Post Patron
7 years ago

Filter column based on another column

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 le...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
7 years ago

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.