Forum Discussion
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
AND [LOOKUPVALUE] = SUBSTRING([CODES_TABLE], 1,1)
) END) as CUSTOMERTYPE,
.... COLUMNY, COLUMNZ
FROM [CUSTOMERS]
Doing the above where the assumption is that the [CODES_TABLE] is another query and the CUSTOMERTYPE
column needs to select from the query in a CASE statement?
Thank you for your response(s) in advance.
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
3 Replies
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523CUSTOMERS 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 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
