Forum Discussion

vickychill's avatar
vickychill
Frequent Visitor
1 year ago
Solved

Convert a filter related Excel Formula into Power Query

Hello,   I am looking for a power query solution for something I have been doing on excel for some time. I have data with >60K rows structured similar to below. "Key" column is already avaialable, ...
Need Help
  • ZhangKun's avatar
    ZhangKun
    1 year ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJVYrViVYyAvKcEvOAsBjMNwby3YsSC1LTikozS8BCJjChYrgOHAxDGMMYxjABM2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Choose a Fruit" = _t]),

    //This section is required
    ChangeKeyType = Table.TransformColumnTypes(Source, {"Key", type text}), 
    dataTable = Table.Distinct(Table.SelectRows(ChangeKeyType, each not (Text.Trim([Choose a Fruit]) = "") or not ([Choose a Fruit] <> null))), 
    dataRecord = Record.FromList(dataTable[Choose a Fruit], dataTable[Key]), 
    fx = (key) => Record.FieldOrDefault(dataRecord, key, null), 
    // section end

    // Use the function here
    Result = Table.AddColumn(ChangeKeyType, "Fruit list", each fx([Key]))


    // After this, you can modify the table at will.
in
    Result
Ahmedx's avatar
Ahmedx
Super User
1 year ago

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJVYrViVYyAvKcEvOAsBjMNwby3YsSC1LTikozS8BCJjChYrgOHAxDGMMYxjABM2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Choose a Fruit" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Choose a Fruit", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Key", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Choose a Fruit"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Choose a Fruit"})
in
    #"Filled Down"