Forum Discussion
Post PartisanHow to use expression (x,y,z)
Community ChampionHi,
_ means current column (or current row value in current column). If you don't understand each _, check this video.
I used {_} because in function List.ReplaceMatchingItems we must declare also first argument of this function as a list so I just put current row value which is _ into a list {}
With Table.TransformColumns function you can't refer other columns - for this purpose you have to use Table.ReplaceValue, but it is not necessary for this purpose, because we unpivoted columns in previous step (and now we have all values for replacing in same column called [Value])
{0}? explanation:
{0} returns first position in list and
? means if that value doesn't exist, return null (without ? it will return an error)
Try to delete {0}? and you will see that it will return a list but I could write it as (but this is just shortcut):
Post PartisanHello dufoq3,
I checked your explanation, very useful. Many thanks.
I am trying now to have the Multireplacement only if a condition is respected. So I tried this code:
(
Input_Table as table,
InputColumnToChange1 as text, //Port type 1-2
InputColumnToChange2 as text, // Body Material
InputColumnToChange3 as text // Stuffing Box Material
) =>
let
Replacements = List.Buffer({{"Bronze", "StSt 316L"}, {"Brass", "StSt 431 / StSt 316L"}}),
AddedIndex = Table.AddIndexColumn(Input_Table, "Index", 0, 1, Int64.Type),
Headers = Table.ColumnNames (AddedIndex),
InputHeader = List.Combine({{InputColumnToChange2}, {InputColumnToChange3}, {"Index"}}),
HeaderDifference = List.Difference(Headers,InputHeader),
UnpivotTable = Table.UnpivotOtherColumns(AddedIndex, HeaderDifference, "Attribute", "Value"),
MultiReplacement = Table.TransformColumns(UnpivotTable,
each if [InputColumnToChange1] = "Butt Welding ASME BPE"
then
{
{"Value", each List.ReplaceMatchingItems(
{_}, Replacements){0}?, type text}}
else _),
PivotTable = Table.Pivot(MultiReplacement, List.Distinct(MultiReplacement[Attribute]), "Attribute", "Value"),
RemoveIndex = Table.RemoveColumns(PivotTable,{"Index"})
in
MultiReplacement
However I got this error message:
I thought it was fine, but I was wrong 😅.
Could you support on this as well?
Thanks.