How to use expression (x,y,z)

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

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.

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.

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"

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.

Hi

will check your explanation. Many Thanks.

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.