Forum Discussion

Blevels's avatar
Blevels
Frequent Visitor
3 years ago
Solved

Power Query CASE to SELECT from another Table

Hello, How does one recreate the following in Power Query: SELECT  COLUMN1, ..., COLUMNX, (CASE WHEN COLUMNX = '' THEN "DNE" ELSE (SELECT [VALUE] FROM [CODES_TABLE] WHERE [POSITION] = 1        ...
  • jennratten's avatar
    jennratten
    3 years ago

    Hello - this will return the expected result.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcw5DsAgDETRu7imsCFkKbNvSk9kcf9rZKCI5MLF+xpZlYQcjTgvnvmm7JQ8OJWUAvNTUwDnsnyFOdXUgItNEVxtasGt/Ip/6sDdrnrwsGkAT5uE4QtHOX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, ColumnX = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"ColumnX", type text}}),
        Result = Table.AddColumn ( 
            #"Changed Type", "CUSTOMERTYPE", each if [ColumnX] = "" then "DNE" else Table.SelectRows(
            Codes, 
            (x)=> 
                x[LookupValue]=Text.Start([ColumnX], 1)
            ){0}[Value], type text 
        )
    in
        Result