Non-hardcoded AddColumn

Question

I'm trying to write a PowerQuery to enable Users to compare to Data Sources (with an unknown number of fields), determined at runtime by the user filling a table.

Of course if the fields are known I can just do multiple Table.AddColumn steps, but with the unknown number of fields I have written a solution using Table.TransformRows but this is slow and doesn't feel like it can be the only way to do it.

let

    // If checks are hardcoded

    Source = Table.FromRecords({

        [FirstName="Aaron", Surname="Dinsdale", Fullname = "Aaron Dinsdale"]

    }),

    Check = Table.AddColumn(Source, "Check Fullname", each if [Fullname] = [FirstName] & " " & [Surname] then "Good" else "Bad"),

 

    // If Checks not hardcoded

    Checks = {"Check Name"}, // Normally, setup by user in table only one here for example

    Source2 = Table.FromRecords({

        [Name1="Aaron", Name2="Dinsdale"]

    }),

    AddCheckColumns = Table.SelectColumns(

                        Source2,

                        List.Combine({

                            Table.ColumnNames(Source2),

                            List.RemoveNulls(

                                List.Transform(

                                    Checks,

                                    each if Text.StartsWith(_, "Check ") then

                                            _

                                         else

                                            null

                                )

                            )

                        }),

                        MissingField.UseNull

                       ),

    ReplaceNull = Table.ReplaceValue(AddCheckColumns, null, "", Replacer.ReplaceValue, Table.ColumnNames(AddCheckColumns)),

    DoCompare = Table.TransformRows(ReplaceNull,

                    (row) as record =>

                        let

                            CompareTransforms = List.RemoveNulls(

                                                    List.Transform(Checks, each

                                                        if Text.StartsWith(_, "Check ") then

                                                            {_, (txt as text) =>

                                                                    let

                                                                        a = Record.Field(row, Text.Replace(_, "Check ", "") & "1"),

                                                                        b = Record.Field(row, Text.Replace(_, "Check ", "") & "2"),

                                                                        rs = if  a = b then

                                                                                "Good"

                                                                             else

                                                                                "Bad"

                                                                    in

                                                                        rs

                                                            }

                                                        else

                                                            null

                                                    )

                                                ),

                            data = Record.TransformFields(row, CompareTransforms)

                       in

                            data

                ),

    Check2 = Table.FromRecords(DoCompare),

 

    // Combine

    Output = Table.Combine({Check, Check2})

in

    Output

The code above provides sample data and produces the correct output. — Daniel Ashby
– Daniel Ashby, Commented Sep 19 at 7:10

Daniel Ashby · Accepted Answer · 2025-09-19 09:22:44Z

2

So the answer is a List.Accumulator function, I had an issue initially as I was checking the whole column but using the Record.Field fixed that issue.

  DoCompare = (a, b) => if a = b then "Good" else "Bad",
  Check3 = List.Accumulator(CompareList, Source, 
  (tbl, checkName) => 
    Table.AddColumn(tbl, check name, each
       DoCompare(
         Record.Field(_, Text.Replace(checkName, "Check ") & "1"),
         Record.Field(_, Text.Replace(checkName, "Check ") & "2")
       )
    )
  )

answered Sep 19 at 9:22

Daniel Ashby

756 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Non-hardcoded AddColumn

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related