Forum Discussion

Mic1979's avatar
Mic1979
Icon for Post Partisan rankPost 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) =>
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

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")

     

27 Replies

    • Mic1979's avatar
      Mic1979
      Icon for Post Partisan rankPost Partisan
      1 year ago

      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's avatar
        dufoq3
        Icon for Community Champion rankCommunity Champion
        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
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Sorry but I need tobother you again.

     

    I am experimenting different solution as I am on the learning curve of Power Query.

     

    I have now this custom function:

     

    (
    Input_Table as table,
    InputColumnToChange1 as text
    ) =>

    let
    List = Input_Table[InputColumnToChange1],
    NewTable = Table.ReplaceValue (
    Input_Table,
    each [List],
    each if [List] = "Bronze"
    then "StSt 316L"
    else [List],
    Replacer.ReplaceText,
    {List}

    )

    in NewTable

     

    I invoke it in this way : 

    let
    Source = #"Query1 (2)"(Summary_Volumes, "Body_Material")
    in
    Source

     

    What I would like to do is to change the values from"Bronze" to "StSt 316L".

     

    However I got this error:

     

     

    Many thanks to all of you helping me.

    This is a wonderful place to learn.

     

     

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      1 year ago

      Why do you need another function?

       

      Replace body of your function with this (as you can see I haven't used let in block because it is not necessary if ther is only 1 step).

       

      (inputTable as table, InputColumnToChange1 as text)=>
Table.ReplaceValue(inputTable, "Bronze", "StSt 316L", Replacer.ReplaceText, {InputColumnToChange1})

       

       

      If you can provide sample data in usable format and expected result based on sample data, maybe we can create whole query for you.

      • Mic1979's avatar
        Mic1979
        Icon for Post Partisan rankPost Partisan
        1 year ago

        My target is to have multiple steps, as written here:

         

        (
        Input_Table as table,
        InputColumnToChange1 as text,
        InputColumnToChange2 as text
        ) =>

        let
        List1 = Input_Table[InputColumnToChange1],
        List2 = Input_Table[InputColumnToChange2],

        NewTable1 = Table.ReplaceValue (
        Input_Table,
        each [List1],
        each if [List1] = "Bronze"
        then "StSt 316L"
        else [List1],
        Replacer.ReplaceText,
        {List1}
        ),

        NewTable2 = Table.ReplaceValue (
        NewTable1,
        each [List2],
        each if [List2] = "Bronze"
        then "StSt 316L"
        else [List2],
        Replacer.ReplaceText,
        {List2}
        )in NewTable2

         

        I want to invoke them in this way:

        let
        Source = #"Query1 (2)"(Summary_Volumes, "Body_Material", "Stuffing_Box_Material")
        in
        Source

         

        but I don't understand why I have the error message:

         

         

        Could you support me on the error?

         

        Thanks.