Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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"

      

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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"
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Nice, it worked.

 

Thank you 🙂