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

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

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!!
- dufoq3
  Community Champion
  1 year ago
  Hi Mic1979,
  
  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.
  
  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
    Post Partisan
    1 year ago
    Many thanks

Forum Discussion

Custom function with Table.TransformRows

Related content

Invoke Custom Function

null input parameters custom function

Integrate Custom Function

DATESYTD function

Refresh with a column containing a custom function