Forum Discussion
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...
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"
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"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"