Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

How to use expression (x,y,z)

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) ...
Tutorial Requests
  • dufoq3's avatar
    dufoq3
    1 year ago

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

    Replace UnPivotedTable step with this:

     

     

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

     

Mic1979's avatar
Mic1979
Post Partisan
1 year ago

Hello dufoq3

 

I have a problem in understanding this part of your code:

 

MultiReplacement = Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each List.ReplaceMatchingItems({_}, Replacements){0}?, type text}}),

 

as per my understanding Table.TransformColumns has the following arguments:

  • UnpivotTable : OK
  • {"Value", each List.ReplaceMatchingItems({_}, Replacements){0}?, type text}} as list. And here I have a bit of problems:
    • "Value" should be the column where to look at to replace : OK
    • List.ReplaceMatchingItems({_}, Replacements) has two argument:
      • {_} what is this?
      • Replacements is a list of lists : OK
    • What does it mean "{0}?" ?
  • How can I make this transformation only if a condition is respected? I tried this:
    • MultiReplacement = Table.TransformColumns(
      UnpivotTable,
      each if [InputColumn] = "DN10" then
      {
      {"Value",
      each List.ReplaceMatchingItems(
      {_},
      Replacements){0}?,
      type text}}
      else _),

but this doesn't work.

 

Thanks.

dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi,

_ 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):

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    Hi

     

    will check your explanation. Many Thanks.

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

    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.