Forum Discussion
Blevels
3 years agoFrequent Visitor
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 ...
- 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
Blevels
3 years agoFrequent Visitor
CUSTOMERS TABLE:
Column1Column2ColumnXCUSTOMERTYPE
| 1 | A | 21200K | |
| 2 | B | 2X300M | |
| 3 | C | AY100X | |
| 4 | D | AY100X | |
| 5 | E | AY100X | |
| 6 | F | 25100X | |
| 7 | G | AY100X | |
| 8 | H | AY100X | |
| 9 | I | AY100X |
CODES_TABLE:
IDPositionLookupValueAttributeValue
| 1 | 1 | 1 | Customer Type | HEALTH CARE |
| 2 | 1 | 2 | Customer Type | AUTOMOTIVE |
| 3 | 1 | 3 | Customer Type | MANUFACTURING |
| 4 | 1 | 4 | Customer Type | TECHNOLOGY |
| 5 | 1 | 5 | Customer Type | PRODUCTION |
| 6 | 1 | 6 | Customer Type | CONSTRUCTION |
| 7 | 1 | 7 | Customer Type | TRADE |
| 8 | 1 | 8 | Customer Type | FINANCE |
| 9 | 1 | 9 | Customer Type | EDUCATION |
| 10 | 1 | A | Customer Type | MILITARY |
EXPECTED OUTCOME:
Column1Column2ColumnXCUSTOMERTYPE
| 1 | A | 21200K | AUTOMOTIVE |
| 2 | B | 2X300M | AUTOMOTIVE |
| 3 | C | AY100X | MILITARY |
| 4 | D | AY100X | MILITARY |
| 5 | E | AY100X | MILITARY |
| 6 | F | 25100X | AUTOMOTIVE |
| 7 | G | AY100X | MILITARY |
| 8 | H | AY100X | MILITARY |
| 9 | I | AY100X | MILITARY |
- jennratten3 years agoSuper User
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