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