Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
How to Filter out highest integer value in power query. ( only in power query which will be compatible in excel too please)
Below is the table I have, I need to filter out highest number which is 16 then keep others, using power query editor in excel.
There will not be any blank, but null's that also need to keep as it is.
Table Name |
null |
1 |
16 |
15 |
null |
15 |
null |
null |
4 |
0 |
4 |
16
|
Solved! Go to Solution.
Thanks, I am kind of new to the power query.
Would you be able to edit below , something is not wokring when I changed. Emp.2 is the table that need to consider.
let
Source = Csv.Document(File.Contents("C:\test.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Emp"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Emp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Emp.1", "Emp.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Emp.1", type text}, {"Emp.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Emp.1"})
in
#"Removed Columns"
You want to get rid of the 16's, is that correct?
Yes, the highest number.
Here is one way:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyhJBmEMoUTGGwwYQJmDRAYhuaKSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[Column1])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Column1] = [Custom] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1"})
in
#"Removed Columns"
Nice, it worked.
Thank you 🙂
Thanks, I am kind of new to the power query.
Would you be able to edit below , something is not wokring when I changed. Emp.2 is the table that need to consider.
let
Source = Csv.Document(File.Contents("C:\test.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Emp"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Emp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Emp.1", "Emp.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Emp.1", type text}, {"Emp.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Emp.1"})
in
#"Removed Columns"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
11 | |
7 | |
6 | |
6 |