Convert a filter related Excel Formula into Power Query

[vickychill]

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.

Key	Choose a Fruit	Fruit list (Automated with formula)
1	Apple	Apples
2	Bananas	Bananas
3	Grapefruit	Grapefruit
4	Grapes	Grapes
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.

[Ahmedx]

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"

[ZhangKun]

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]

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 · Accepted solution]

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