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, user selects Column "Choose a Fruit" and  Column "Fruit list" get automatically generated. I have been using excel formula for Column "Fruit list"  as 

=UNIQUE(FILTER([Choose a Fruit],([Key]=[@Key])*([Choose a Fruit]<>"")))

and drag down so it work perfectly. But everything get super slow in excel with too many rows so I am thinking of a Power Query solution.

KeyChoose a FruitFruit list (Automated with formula)
1AppleApples 
2BananasBananas
3GrapefruitGrapefruit
4GrapesGrapes
2 Bananas
2 Bananas
2 Bananas
1 Apples 
3 Grapefruit
4 Grapes

Can some suggest any way to achieve this in Pwer Query, I could not figure it out. Any alternatives suggestion will also be great.

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

4 Replies

  • ZhangKun's avatar
    ZhangKun
    Super User
    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]),
    // The Key column must be set to text first, and can be converted to other types after the calculation is completed
    ChangeType = Table.TransformColumnTypes(Source,{{"Key", type text}}), 

    // To prevent the input of spaces or other empty text, you must remove the blanks before checking
    dataTable = Table.Distinct(Table.SelectRows(ChangeType, each not (Text.Trim([Choose a Fruit]) = "") or not ([Choose a Fruit] <> null))), 
    dataRecord = Record.FromList(dataTable[Choose a Fruit], dataTable[Key]), 

    // fill in the new column
    //Result = Table.AddColumn(ChangeType, "Fruit list", each Record.FieldOrDefault(dataRecord, [Key], null))

    // replace in the original column
    Result = Table.ReplaceValue(ChangeType, null, each Record.FieldOrDefault(dataRecord, [Key], null), (v, old, new) => new, {"Choose a Fruit"})
in
    Result
    • vickychill's avatar
      vickychill
      Frequent Visitor
      1 year ago

      Thank you Zhangkun, Can you help me with this part? I could not understand how to put this in a new column.

          // fill in the new column
    //Result = Table.AddColumn(ChangeType, "Fruit list", each Record.FieldOrDefault(dataRecord, [Key], null))

    // replace in the original column
    Result = Table.ReplaceValue(ChangeType, null, each Record.FieldOrDefault(dataRecord, [Key], null), (v, old, new) => new, {"Choose a Fruit"})
in
    Result

       

      • ZhangKun's avatar
        ZhangKun
        Super User
        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"