Forum Discussion

Whoule's avatar
Whoule
Helper I
2 years ago
Solved

Conditonal Column based on a column from another table

I need to create a formula that compare the start of the value in column YY to the value in column XX from another table called StatusList. Here what I have so far: each if Text.StartsWith([YY], StatusList[XX]) then "Open" else "remove"

 

Issue now is that I receive this error message: Expression.Error: We cannot convert a value of type List to type Text.

 

Attached is a sample of the data.

 

Thank you.

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Whoule,

     

    replace Source[XX] with your Table[XXColum] reference

     

     

    Result:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncq7EQAhCADRXoglgONbi0P/bZyiZjtvdk5gks+QmGHchhrNGfhU46pZOLLmZt59mF0TifrufuyCJEdXQtUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YY = _t, XX = _t]),
    Ad_Check = Table.AddColumn(Source, "Check", each if List.Contains(Source[XX], Text.BeforeDelimiter([YY], "-") & "-") then "Open" else "Remove", type text)
in
    Ad_Check

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Whoule,

     

    replace Source[XX] with your Table[XXColum] reference

     

     

    Result:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncq7EQAhCADRXoglgONbi0P/bZyiZjtvdk5gks+QmGHchhrNGfhU46pZOLLmZt59mF0TifrufuyCJEdXQtUP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YY = _t, XX = _t]),
    Ad_Check = Table.AddColumn(Source, "Check", each if List.Contains(Source[XX], Text.BeforeDelimiter([YY], "-") & "-") then "Open" else "Remove", type text)
in
    Ad_Check