How to I dynamically reference a column when adding a calculated column?

[Tooley]

Hi All,

For this question my main purpose is to replace a column containing text such as Yes/No with a logical column. Rather than use a replace text method, I'm trying to achieve by adding a Custom calculated column, via a function. I'm struggling to figure out how to dynamically refer to a column for the formula/expression in the following step:

AddedTempCol = Table.AddColumn(Source, "TempCol", each [Complete] = TrueValue, type logical),

to something like....

AddedTempCol = Table.AddColumn(Source, "TempCol", each "[" & ColumnName & "]" = TrueValue, type logical),

Hopefully the attached is clear enough?


I'm very new to M language so I'd be interested to hear what would be considered the best approach to achieve the same, but if different I'd still like to understand how to dynamically add column names when creating the formula/expression as can see I may need this in the future.

Thanks in advance

 

[Tooley]

Hi All,

After a bit of pain lol, I've managed to work out the solution to my own problem (to reference a dynamic column name in the formula/expression when adding a custom column):

From..

AddedTempCol = Table.AddColumn(Source, "TempCol", each [Complete] = TrueValue, type logical),

To..

AddedTempCol = Table.AddColumn(Source, "TempCol", each (Record.Field(_,ColumnName) = TrueValue), type logical),

I'll leave unsolved for a day or so to give time to see if there is a better approach to convert a text column of Yes/No to a logical column. The function I now have is "fnTransformColumnToLogical"...

let func =
        (
            Source as table,
            ColumnName as text,
            TrueValue as text
        )   =>






        let
            ListOrigCols = Table.ColumnNames(Source),
            AddedTempCol = Table.AddColumn(Source, "Temp" & ColumnName, each (Record.Field(_,ColumnName) = TrueValue), type logical),
            RemovedOrigCol = Table.RemoveColumns(AddedTempCol,{ColumnName}),
            RenamedTempCol = Table.RenameColumns(RemovedOrigCol,{{"Temp" & ColumnName, ColumnName}}),
            ReorderedColumnsToOriginal = Table.ReorderColumns(RenamedTempCol,ListOrigCols)
        in
        ReorderedColumnsToOriginal,




    /*======================================================================================================================================
      DOCUMENTATION SECTION


      Developer Note : Unable to show both field and function documentation together, so choose preferred....
                       Field Documentation        =     Rem out all lines in the Function documentation section
                       Function Documentations    =     Do not rem out all lines in the Function documentation section
    --------------------------------------------------------------------------------------------------------------------------------------*/


    funcType = type function
        (


    //Field Documentation Section - Start...................................................................................................


            Source as 
                (    type table meta
                        [
                            //Documentation.AllowedValues = 
                            Documentation.FieldCaption = "Source",
                            Documentation.FieldDescription = "The table to modify.",
                            Documentation.SampleValues = "MyTable"
                        ]
                ),
            ColumnName as 
                (    type text meta
                        [
                            //Documentation.AllowedValues = 
                            Documentation.FieldCaption = "ColumnName",
                            Documentation.FieldDescription = "The name of the table column to modify.",
                            Documentation.SampleValues = "MyColumn"
                        ]
                ),
            TrueValue as 
                (    type text meta
                        [
                            //Documentation.AllowedValues = 
                            Documentation.FieldCaption = "TrueValue",
                            Documentation.FieldDescription = "The value to be conterted to TRUE.",
                            Documentation.SampleValues = """Yes"" / ""Y"""
                        ]
                )
        )   as table




    //Field Documentation Section - End.....................................................................................................




    //Function Documentation Section - Start................................................................................................


            meta
            [
                Documentation.Name = "fnTransformColumnToLogical",
                Documentation.Description = "A function that transforms a column to logical data type, with existing data converted to TRUE or FALSE.",
                Documentation.LongDescription = "This function requires three parameters, Source, ColumnName and TrueValue.  
                                                 Values in the original ColumnName that match the TrueValue are converted to TRUE, whilst others are converted to FALSE.",
                Documentation.Category = "Transform Column",
                Documentation.Examples =
                    {    
                        [
                            Description = "This workbook has a table called ""MyTable"", 
                                           with a column named ""MyColumn"" whose values are either ""Yes"" or ""No"", which need to be convverted to TRUE or FALSE.",
                            Code = "fnTransformColumnToLogical(MyTable,""MyColumn"",""Yes"")",
                            Result = "Table is returned with the Column converted to a logical data type (Yes to TRUE/other values to FALSE), with original column order maintained."
                        ]
                    }
            ]


    //Function Documentation Section - End..................................................................................................




in Value.ReplaceType(func, funcType)

Thanks

 

[horseyride]

It appears you want to convert Yes/No to True/False for all columns except the first one

let Source = OriginalTable,
    List = List.Skip(Table.ColumnNames(Source),1),
    TransformedColumn =  Table.TransformColumns(Source, List.Transform(List , each {_, (inner) => if inner = "Yes" then true else false , type logical}))
in TransformedColumn

or in one line

let
    Source = OriginalTable,
    TransformedColumn =  Table.TransformColumns(Source, List.Transform( List.Skip(Table.ColumnNames(Source),1) , each {_, (inner) => if inner = "Yes" then true else false , type logical}))
in TransformedColumn

 

[Tooley]

Hi Horseyride,

Whilst I did have multiple columns to convert, it was more for demonstration purposes. I do prefer your method a lot more so will replace the code in my function using a list of column names instead.

I only started using PQ this month and modifying M language in last 2 weeks, so I was initially confused by the use of the word 'inner' (thinking of table joins) and => (thinking of equal to or greater than). Then a light bulb came on when I noticed the the parenthesis, understanding ()=> to be a function call in PQ. This lead me to rebuild your code into the following longer version so I can better understand what's going on (the reason for my delay in responding):

let 
   Source = OriginalTable,
   List = List.Skip(Table.ColumnNames(Source),1),
   TransformedColumn = Table.TransformColumns
      (
         Source, 
         List.Transform
            (
               List, 
               each 
                  {
                     _,
                     let funcNested = (inner) =>
                          let 
                             FieldValue = if inner = "Yes" then true else false
                          in 
                             FieldValue 
                     in funcNested,
                     type logical
                  }
            )
      )
in
   TransformedColumn

Long winded I know, but I always want to understand whats going on before using any code. This has helped me translate into :

• Only modify column names in the 'Source' table that are in the 'List'
• For all of their rows, use a 'function nested within the step' to check if the row field value equals "Yes" then return TRUE otherwise FALSE, storing results in a temporary list
• Transform the column type to logical, replacing original field values with those in the temporary list

Am I on the right track?

Thanks

 

[horseyride]

Not sure how to answer, as I don't understand, I guess, the goal.
What do you want to do? Create a function to ____ or something else?
Best

 

[Tooley]

Sorry if it wasn't clear. I'm asking what the code you gave me is doing. Is my understanding correct?

Thanks

 

[horseyride]

yes