Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Filter out highest integer value in power query

 

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
 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

  

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

You want to get rid of the 16's, is that correct?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Nice, it worked.

 

Thank you 🙂

Anonymous
Not applicable

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"

  

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors