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]

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]

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]

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.

[lbendlin]

Just pointing out that Replacer.ReplaceText is likely not what you want.

[dufoq3]

If you want my help I don't need your query. I need sample data and expected result based on that.

[Mic1979]

Sorry, how to add a sample file?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Mic1979]

Hello,

here the link : 

Book1.xlsx

This is just an abstract of my file, that has almost 500.000 rows. 

The target of the function is to get the table in the right. Probably it is not the most efficient way to do that, but understanding why it is not working with a custom function will help me with other cases in the future.

Thanks.

[lbendlin]

That's a personal Onedrive and requires a login.  Please use a public file share service instead.

[Mic1979]

https://docs.google.com/spreadsheets/d/1TaWICo7Bw1QQHqkrNdPakoBDLX1WeSeu/edit?usp=drive_link&ouid=10...

[lbendlin]

Sorry, still asks for access.

[Mic1979]

now it should work, I changed the rights to access:

https://docs.google.com/spreadsheets/d/1TaWICo7Bw1QQHqkrNdPakoBDLX1WeSeu/edit?usp=sharing&ouid=10598...

[dufoq3]

Why do you need function? You can replace separately. 

1st replace Bronze and afterwards replace Brass

[Mic1979]

Hello 

I want to manage this through a function because I want to use this custom function also for other columns, where I can change the content of the second and third column based on the values of the first.

I think the custom function I wrote could cover my target, but I really don't understand the reasonof the error message I get. 

Hoping this explains my willing.

Many thanks for your support.

[dufoq3]

@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]

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.

[Mic1979]

Hello

I already used this type of codes with unpivoting and the pivoting, but it worked very well with small tables. In my case, where the table is vary big (more than 500.000 rows) the query is running for many hours and I cannot use it. Tht's why I was looking for an alternative code.

I do hope I did transfer my message.

Thanks.

[dufoq3]

500 000 rows is a lot for power query - but it should not take hours. Try to find which step is the most demanding. 

I've just tested it. It takes around 1min 15sec to multiply your sample to 500 000rows.

Same query with additional replacements for 5 columns takes same time so this should not be an issiue.

I've just buffered Replacements list.

Test this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEzNFDSUXIqys+rSgUzEouLlWJ1RoxMLAA=", 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]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Body_Material", "Body_Material - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Stuffing_Box_Material", "Stuffing_Box_Material - Copy"),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Body_Material", "Body_Material - Copy.1"),
    TableRepeat = Table.Repeat(#"Duplicated Column2", 50000),
    Replacements = List.Buffer({{"Bronze", "StSt 316L"}, {"Brass", "StSt 431"}}),
    // This step is mandantory for Pivoting back later.
    #"Added Index" = Table.AddIndexColumn(TableRepeat, "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({_}, Replacements){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]

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]

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