Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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.
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.
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.
OK, thanks. Is there a data sample?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |