Custom function with Table.TransformRows

[Mic1979]

Dear all,

I have the sample data at the following link:

https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?usp=docslist_api&filetype=msex...

I have the following function to transform the rows based on a condition:

(data, replacements, condition_column, condition_value, columns_list as list) =>
[
// generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),
// replace values in rows
replace = Table.TransformRows(
data, (x) => if Record.Field(x, condition_column) = condition_value
then Record.TransformFields(x, transformations)
else x
),
result = Table.FromRecords(replace)
][result]

invoked in this way:

transform_rows(data_table, replacements_table, "Port_Type_1_2", "Butt Welding ASME BPE", {"Body_Material", "Stuffing_Box_Material"})

The problem of this function is that I would like to have more than one value in the argument "condition_value", while at the moment the code is structured only with one value. To be clear, instead of having e.g. "Butt Welding ASME BPE", I would like to have a list of values: {"Butt Welding ASME BPE",""Clamp ASME BPE}.

I tried to change the argument condition_value from text to list, but it is not working.

Could you support me?

Thanks.

[anmolmalviya05 · Accepted solution]

Hi @Mic1979, Can you please try the below code:

(data, replacements, condition_column, condition_values as list, columns_list as list) =>
let
// Generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),

// Replace values in rows
replace = Table.TransformRows(
data,
(x) => if List.Contains(condition_values, Record.Field(x, condition_column))
then Record.TransformFields(x, transformations)
else x
),

result = Table.FromRecords(replace)
in
result

[dufoq3 · Accepted solution]

Hi @Mic1979, another solution without custom function:

Before

After

v1

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Transformed = Table.TransformRows(StepBack, each
        if List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2])
        then List.Accumulate(Record.FieldNames(_), _, (s,c)=> Record.TransformFields(s, {{c, (y)=> Record.FieldOrDefault(R, Record.Field(_, c), y) }}))
        else _ ),
    ToTable = Table.FromRecords(Transformed, Value.Type(StepBack))
in
    ToTable

v2

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Replaced = Table.ReplaceValue(StepBack, 
        each List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2]),
        null,
        (x,y,z)=> if y then Record.FieldOrDefault(R, x, x) else x,
        Table.ColumnNames(StepBack) )
in
    Replaced

[Mic1979]

Is it possible to extend this to more columns?

I will explain better:

I have the sample data in the link below:

https://docs.google.com/file/d/1yJGxTqikuKoGMwZEshory8ue6vmumcU-/edit?usp=docslist_api&filetype=msex...

I would like to make different changes in the columns Digital_Output and Digital_Communication_Protocol,

after check on them. For example:

if [Digital_Output] ="NO OUTPUT"  and [Digital_Communication_Protocol] = "IO Link"

then [Digital_Output]  = "NPN / PNP" and [Digital_Communication_Protocol] = "NOT APPLICABLE"
else [Digital_Output] and [Digital_Communication_Protocol]

is it possible to restructure the code posted before and accepted as solution to meet this target?

Thanks a lot.

[dufoq3]

Of course it is possible 🙂

I did it my way, but if I can give you advice. To achieve the same - just create 2 new columns like Digital_OutputNEW and Digital_Communication_ProtocolNEW new based on basic if condition (use CONDITIONAL COLUMN from USER INTERFACE if you are not familiar with syntax) and afterwards just delete old columns and rename new ones... This is how you can achieve the same with no query speed impact.

Output

let
    FileLink = Web.Contents("https://docs.google.com/uc?export=download&id=1yJGxTqikuKoGMwZEshory8ue6vmumcU-"),
    ExcelWorkbook = Excel.Workbook(FileLink),
    Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data])),
    Transformed = Table.FromRecords(Table.TransformRows(Source, each _ & (
        if [Digital_Output] = "NO OUTPUT" and [Digital_Communication_Protocol] = "IO Link"
        then [Digital_Output = "NPN / PNP", Digital_Communication_Protocol = "NOT APPLICABLE"]
        else [] )), Value.Type(Table.FirstN(Source, 0)) )
in
    Transformed

[Mic1979]

Hello @dufoq3 

really appreciating your feedbacks.

Concerning your first question, I am using Power query for my daily job, and I am taking this as opportunity to be more familiar with the syntax.

coming back to your code:

1. what is the purpose of this line code: Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data]))
2. what is the purpose of : each _ &  

Many thanks again, you are great!!

[dufoq3]

Hi @Mic1979,

1. as you can see I'm connecting to your file directly on OneDrive, so the purpose of Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data])) is to get table in desired format.
   
   
2. in Table.TransformRows we are manipulating records. You can update record like this:
   
   [A = 1, B = 2] & [A = 5, C = 100]
   
   Output of code above will be:
   [A = 5, B = 2, C = 100] so A will be updated and C will be added.
   
   Check this article if you want to know more about records.

[Mic1979]

Hello @dufoq3 

more questions (sorry):

1. why you added "Value.Type(Table.FirstN(Source, 0)"
2. I tried to change your code as below: 

   (
   data,
   Dig_Output_Column,
   Dig_Comm_Protocol_Column
   // Dig_Output_Condition_Value as list,
   // Dig_Comm_Protocol_Condition_Value as list
   ) =>

   let
   Transformed = Table.FromRecords(Table.TransformRows(
   data, each _ & (
   if Dig_Output_Column = "NO OUTPUT" and Dig_Comm_Protocol_Column = "IO Link"
   then [Dig_Output_Column = "NPN / PNP", Dig_Comm_Protocol_Column = "NOT APPLICABLE"]
   else [] )), Value.Type(Table.FirstN(data, 0)) )
   in
   Transformed

   It gives me no errors, but it is not doing the replacement.
3. In case I needed to also the values as variables, can I do as following: [Digital_Output] =  Dig_Output_Condition_Value  ?

Thanks

[dufoq3]

1.) to restore data types

2.) why do you need it as a separate function? Will you use it for more then 1 table?
3.) if you need more variables - you can use if List.Contains({"var1", "var2", var3"}, [Your_Column]) then ... else ...

[Mic1979]

1. OK

2. Yes I need to use this for different cases

3. Will try

Can I come back to you for the point 3. if I won't be able to make fine code?

Many thanks

[dufoq3 · Accepted solution]

Function - you can cut out F function and paste it into a new query to make it usable for whole document

let
    FileLink = Web.Contents("https://docs.google.com/uc?export=download&id=1yJGxTqikuKoGMwZEshory8ue6vmumcU-"),
    ExcelWorkbook = Excel.Workbook(FileLink),
    Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data])),
    F = (tbl as table, DO_col as text, DCP_col as text, DO_cond as list, DCP_cond as list)=>
        Table.FromRecords(Table.TransformRows(tbl, each
                if List.Contains(DO_cond, Record.Field(_, DO_col)) and List.Contains(DCP_cond, Record.Field(_, DCP_col))
                then Record.TransformFields(_, {{DO_col, (x)=> "NPN / PNP"}, {DCP_col, (x)=> "NOT APPLICABLE"}})
                else _ ), Value.Type(Table.FirstN(Source, 0)) ),
    InvokedF = F(Source, "Digital_Output", "Digital_Communication_Protocol", {"NO OUTPUT", "condition2", "condition3..."}, {"IO Link", "condition2", "condition3..."})
in
    InvokedF

[Mic1979]

Hello @dufoq3 

as usual, it works.

Thanks a lot.

[dufoq3]

You're welome @Mic1979, enjoy 😉

[Mic1979]

Many thanks

[dufoq3 · Accepted solution]

Hi @Mic1979, another solution without custom function:

Before

After

v1

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Transformed = Table.TransformRows(StepBack, each
        if List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2])
        then List.Accumulate(Record.FieldNames(_), _, (s,c)=> Record.TransformFields(s, {{c, (y)=> Record.FieldOrDefault(R, Record.Field(_, c), y) }}))
        else _ ),
    ToTable = Table.FromRecords(Transformed, Value.Type(StepBack))
in
    ToTable

v2

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Replaced = Table.ReplaceValue(StepBack, 
        each List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2]),
        null,
        (x,y,z)=> if y then Record.FieldOrDefault(R, x, x) else x,
        Table.ColumnNames(StepBack) )
in
    Replaced

[Mic1979]

It works also!!

Thanks.

[dufoq3]

You're welcome @Mic1979, enjoy 😉

[anmolmalviya05 · Accepted solution]

Hi @Mic1979, Can you please try the below code:

(data, replacements, condition_column, condition_values as list, columns_list as list) =>
let
// Generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),

// Replace values in rows
replace = Table.TransformRows(
data,
(x) => if List.Contains(condition_values, Record.Field(x, condition_column))
then Record.TransformFields(x, transformations)
else x
),

result = Table.FromRecords(replace)
in
result

[Mic1979]

It works!!!

Thanks

[Mic1979]

Hello,

sorry but I can highlight the difference between my code and yours.

Could you clarify?

Thanks.

[dufoq3]