How to use expression (x,y,z)

[Mic1979]

Dear all,

does anyone know if there is some resource to understand how to use 

For example, I got in this forum indications to write the following custom function:

(Input_Table as table) =>
let
NewTable = Table.ReplaceValue(
Input_Table, (x)=>x,(x)=>x, (x,y,z)=> if x = null then "-" else x, Table.ColumnNames(Input_Table)) 

in NewTable

Thanks in advance

[Mic1979]

Hello 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.

[Mic1979]

Hi

will check your explanation. Many Thanks.

[lbendlin]

Replace Values in Power Query M (Ultimate Guide) - BI Gorilla

[Mic1979]

Thanks a lot.

I was trying to write a custom function based on this guide.

My target is to use this custom function for different scope, this is the reason why I want to keep it as general as possible.

However I already met the first problem. 

What I wrote is:

(
Input_Table as table,
InputColumn1 as text,
InputColumn2 as text
) =>

let
AddIndex = Table.AddIndexColumn (Input_Table, "Index", 1,1),
UnPivotedTable = Table.UnpivotOtherColumns (AddIndex, {"InputColumn1", "InputColumn2"}, "Attribute", "Value")

in UnPivotedTable

I  invoked it in this way:

let
Source = Query1(Summary_Volumes, "Body_Material", "Struffing_Box_Material")
in
Source

This gives an error message I really don't understand:

Could you help me in understanding why this is not working?

Thanks a lot in advance

[dufoq3 · Accepted solution]

Hi @Mic1979, you hardcoded "InputColumn1" and "InputColumn2" in UnPivotedTable step:

Replace UnPivotedTable step with this:

Table.UnpivotOtherColumns (AddIndex, {InputColumn1, InputColumn2}, "Attribute", "Value")

[Mic1979]

Great,

many thanks

[dufoq3]

You're welcome.