Dynamic Custom Column in Power Query "M"

[zzzzzzzzzz]

Hi all,

I'm trying to create a dynamic tool that references end user input in the query and performs operations on the source data.

Right now I have a table on a spread sheet where a user can provide input which is then called in the query to create a custom column. The inputs correspond to column names in the query. Example:

let

Source = *Underlying Table*

ColumnName1 = Input1,
ColumnName2 = Input2,
ColumnName3 = Input3,

CreateCustomColumn = Table.AddColumn(Source, "CustomColumn1", each "["&ColumnName1&"]" = "["&ColumnName2&"]")

in
CreateCustomColumn


I want it to return the value as true or false depending on if the records match. The issue is that once it evaluates "["&ColumnName1&"]" and it compares it as a string and not the fields of each record. I haven't found a way for it to recognize them as records. Help!

(Side note: language "M" kinda blows.. no loops?!)

 

[scottsen]

Oh man. No way we get through this one with Chris Webb.

 

[RoryA]

You could do this by duplicating the columns you want to compare (since the DuplicateColumn function allows you to specify the source column as a String), then creating a calculated column comparing the two duplicated columns - using the fixed names you can give the duplicates - then hide the duplicates from the output. So something like this (Table2 is the query that returns your column heading tables):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate1 = Table.DuplicateColumn(Source, Table2{0}[Column1],"MatchCol1" ),
    Duplicate2 = Table.DuplicateColumn(Duplicate1, Table2{1}[Column1],"MatchCol2" ),
    AddCalcCol = Table.AddColumn(Duplicate2, "Matches", each [MatchCol1]=[MatchCol2]),
    FinalTable = Table.SelectColumns(AddCalcCol,{"Header1", "Header2", "Header3", "Header4", "Header5", "Header6", "Header7", "Matches"})

in
    FinalTable

There's probably a much neater M solution though!

 

[zzzzzzzzzz]

Great suggestion! It's working for my purposes so far but I'm using a relatively small amount of data. We'll see how long it takes to run when I feed it a bit more.

Thanks!

 

[RoryA]

Glad to help, and welcome to the forum!