In my previous post I wrote about one interesting technique used by my colleague Zoltán Kaszaki-Krsjak.
Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", each Buffer[Category]{[ID2]}?),
// It helps to shift values of column [Category] one row upwards.
It is a trick that you most probably will not use in any of your solutions. However, worth to know how it works and how to do this without adding new columns.
“each Buffer[Category]{[ID2]}?” is just a function, so we can combine it with methods described in one of my posts (Transform Column Using Custom Function).
Here is the code.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Category", "Method1"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Category", "Method2"),
#"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Category", "Method3"),
Buffer = Table.Buffer( #"Duplicated Column2" ),
Method1 = Table.FromRecords( Table.TransformRows( Buffer, each [Index = [Index],
Category = [Category],
Method1 = Buffer[Method1]{[Index]}?,
Method2 = [Method2],
Method3 = [Method3] ] ) ),
Method2 = Table.FromRecords( Table.TransformRows( Method1, (row) =>
Record.TransformFields( row,
{"Method2", each Buffer[Method2]{ row[Index] }? } ) ) ),
// method offered by Miguel Escobar in comment to previous post
Method3 = Table.ReplaceValue(Method2, each [Method3], each Buffer[Method3]{[Index]}?, Replacer.ReplaceValue, {"Method3"})
in
Method3
File with methods is here.
But which method is faster?
