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

     

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

Mic1979, lbendlin posted a link with Rick's great explanation. I can recommend you to focus on part with unpivoting. With this technique you can replace all unpivoted columns at once. If you want to replace more pairs in one step - you can do it this way:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEzNFDSUXIqys+rSgUzEouLlWJ1RmUGgUwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DN_Size = _t, Body_Material = _t, Stuffing_Box_Material = _t]),
    // This step is mandantory for Pivoting back later.
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"DN_Size", "Index"}, "Attribute", "Value"),
    MultiReplacement = Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each List.ReplaceMatchingItems({_}, {{"Bronze", "StSt 316L"}, {"Brass", "StSt 431"}}){0}?, type text}}),
    #"Pivoted Column" = Table.Pivot(MultiReplacement, List.Distinct(MultiReplacement[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

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

Probably my problem was that I did not use your code entirely. As this is a little bit tough for me to understand, I am implementing it step by step.

 

This is the code:

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

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEzNFDSUXIqys+rSgUzEouLlWJ1RmUGgUwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [InputColumnToChange1 = _t, InputColumnToChange2 = _t]),

AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)

in
AddedIndex

 

and this is how I am invoking it:

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

 

But I got this as output:

 

What is my mistake?

 

Thanks.