Forum Discussion

Mic1979

Post Partisan

1 year ago

Solved

Custom function with Table.TransformRows

Dear all,

I have the sample data at the following link:

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

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.

Need Help

anmolmalviya05
1 year ago
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

1 year ago

Hi Mic1979, another solution without custom function:

Before

After

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

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

dufoq3

1 year ago

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

18 Replies

anmolmalviya05
Super User
1 year ago
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
  Post Partisan
  1 year ago
  Hello,
  
  sorry but I can highlight the difference between my code and yours.
  Could you clarify?
  
  Thanks.
  - dufoq3
    Community Champion
    1 year ago
- Mic1979
  Post Partisan
  1 year ago
  It works!!!
  Thanks

dufoq3

Community Champion

1 year ago

Hi Mic1979, another solution without custom function:

Before

After

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

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
Post Partisan
1 year ago
It works also!!
Thanks.
- dufoq3
  Community Champion
  1 year ago
  You're welcome Mic1979, enjoy 😉

Mic1979
Post Partisan
1 year ago
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=msexcel

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
  Community Champion
  1 year ago
  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
    Post Partisan
    1 year ago
    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:
    what is the purpose of this line code: Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data]))
    what is the purpose of : each _ &
    Many thanks again, you are great!!

Forum Discussion

Custom function with Table.TransformRows

18 Replies

Related content

Invoke Custom Function

null input parameters custom function

Integrate Custom Function

DATESYTD function

Refresh with a column containing a custom function