Forum Discussion

rpiboy_1's avatar
rpiboy_1
Helper V
3 years ago
Solved

Return column by Position in if statement

Hello all,

 

I'm attempting to write a dynamic function (and I know I've had this problem before). Where I need to return the Column Name in an if function by posistion. I've tried the following code and it is failing on the Column Name. The statement itself does return the correct Text value, but as I recall there is something 'special' I have to do when in the context of an 'each' and an IF statement.

 

So here is the statement from my Function. Pretty straight forward, adding a column and the new value for each record should be based upon evaluating the column as defined by the "Table.ColumnNames"

 

    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", ColumName, each if Table.ColumnNames(#"Renamed Columns"){#"ColumnPosistion"} = "N" then false else null, type logical)

 

 
In testing, the statement below does return the expected text string value for the target column name. Based on the fact that I'm only getting Nulls as a result (the ELSE value) that tells me the whole statement is not properly evaluating the target column. I already proved everything in a non-dynamci way, so I'm pretty confident its my column refernce that is the problem here.

 

#"Column" = Table.ColumnNames(#"Renamed Columns"){#"ColumnPosistion"},

 

 

For completeness, the whole function as it currently stands (the expected values in the column intended to be evaluated are Y, N or null), when I know its working properly, I'll change the ELSE condition result to TRUE (in my case nulls should be considered to be TRUE):

 

(Tbl as table, ColumName as text) =>
let
    #"NewColumnName" = ColumName & "org",
    #"Renamed Columns" = Table.RenameColumns(Tbl,{{ColumName, #"NewColumnName"}}),
    #"ColumnNames" = Table.ColumnNames(#"Renamed Columns"),
    #"ColumnPosistion" = List.PositionOf(#"ColumnNames", #"NewColumnName"),
    #"Column" = Table.ColumnNames(#"Renamed Columns"){#"ColumnPosistion"},//for testing
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", ColumName, each if Table.ColumnNames(#"Renamed Columns"){#"ColumnPosistion"} = "N" then false else null, type logical)
in
    //#"Renamed Columns"
    //#"ColumnPosistion"
    //#"Column"
    #"Added Conditional Column"

 

 

Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    I think in your case it will be Recrod.Field. You do not need to dance around getting the column position or anything, just pass the desired column (in this case it will become a field's) name:  

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.Field(_,"Column1")+1)
in
    #"Added Custom"

     

    Cheers,

    John

6 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi rpiboy_1,

     

    From what I can see the test below should always return false?

    Table.ColumnNames(#"Renamed Columns"){#"ColumnPosistion"} = "N"

     This returns the column name in the ColumnPosition. Which is a position of the NewColumnName. So the Table.ColumnNames(RenamedColumns){ColumnPosition} will always return NewColumnName (as text). And we know that it at least contains "org", so I can't be = "N" in any case.

     

    Do you agree?

     

    Cheers,

    John

    • rpiboy_1's avatar
      rpiboy_1
      Helper V
      3 years ago

      Hi John,

       

      I agree, the name of the column will fail the test. I'm trying to return the contents of said column. So for example, lets say the source table for the function has a column with the name 'Field'

      Field

      Y

      N
       
      Y

       

      First the function renames this column to be 'Fieldorg'.

      Next, I want to add a column and test each row in the column, does it equal N?

       

      Fieldorg

      		Field
      YTRUE
      NFALSE
       TRUE
      YTRUE

       

      However, something is obviously failing in my ability to test each row in the target column.

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        I think in your case it will be Recrod.Field. You do not need to dance around getting the column position or anything, just pass the desired column (in this case it will become a field's) name:  

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.Field(_,"Column1")+1)
in
    #"Added Custom"

         

        Cheers,

        John

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

     

    try to amend your code like this

     

    (Tbl as table, ColumName as text) =>
let
    #"NewColumnName" = ColumName & "org",
    #"Renamed Columns" = Table.RenameColumns(Tbl,{{ColumName, #"NewColumnName"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", ColumName, each if Record.Field(_,#"NewColumnName") = "N" then false else null, type logical)
in
    //#"Renamed Columns"
    //#"ColumnPosistion"
    //#"Column"
    #"Added Conditional Column"

     

     

    may this can resolve the problem directly if you just want transform the value in the some columns

    (Tbl as table, ColumName as text) =>
Table.TransformColumns(Tbl, {ColumName, each if _ = "N" then false else null})