Multiple Conditions Power Query

[PMR18]

Hi folks,

Hope someone can help me with this...

I need a column with multiple conditions but can´t figure it out if it is posible.

1. If Bank is empty and Promo starts with "P BEN" else "SAB00" then "CRN00" but...

2. If Bank is empty and Promo starts with "P ALT" else "SAB01" then "CRN01" but...

3. If Bank is empty and Promo starts with "P AÑO" else "SAB02" then "CRN02" but...

Other way could be 

1. If Bank is empty and Company "A" and Promo starts with "P BEN" else "SAB00" then "CRN00" but...

2. If Bank is empty and Company "B" and Promo starts with "P ALT" else "SAB01" then "CRN01" but...

3. If Bank is empty and Company "C" and Promo starts with "P AÑO" else "SAB02" then "CRN02"

Thanks in advance for your help.

[dufoq3]

Hi @PMR18, you can edit or add more conditions here:

Result

let
    TableData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5NLCpR0lFyBGJ/JwVHnxDXID/HEM8wV6VYHYQ0dln/3NQ8oIwTEAcoOLn6OUYEI4uDhV2CgjDEQOa4umIII5ngkVoJFHIG4mBXZyQRuHagobEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Bank Name" = _t, Promo = _t]),
    TableHelper = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5NLCpR0lHyd1Jw9AkBMoIdnRQMDAyVYnWilfxzU/OAQgEKTq5+cCkjsJRHaiVYBlmTsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Promo Text" = _t, SAB = _t]),
    ReplaceBlankToNull = Table.TransformColumns(TableData, {}, each if Text.Trim(_) = "" then null else _),
    Ad_PromoHelper = Table.AddColumn(ReplaceBlankToNull, "PromoHelper", each Text.Start([Promo], 5), type text),
    MergedQueries = Table.NestedJoin(Ad_PromoHelper, {"Company"}, TableHelper, {"Company"}, "TableHelper", JoinKind.LeftOuter),
    ExpandedTableHelper = Table.ExpandTableColumn(MergedQueries, "TableHelper", {"Promo Text", "SAB"}),
    Ad_FinalColumn = Table.AddColumn(ExpandedTableHelper, "FinalColumn", each
            if [Bank Name] = null and [PromoHelper] = [Promo Text] then [SAB] else Text.Replace([SAB], "SAB", "CRN"), type text),
    RemovedColumns = Table.RemoveColumns(Ad_FinalColumn,{"PromoHelper", "Promo Text", "SAB"})
in
    RemovedColumns

[PMR18]

Hi @dufoq3,

Thanks for the quick answer.

The request was a bit confusing.

Lets see if it is easier to understand.

The image below is the normal table.

  
And the image below is the expected result.

I was trying to get the expected result in Power Query.  

The conditions are:

1) If the company Smart has the Bank Name empty and the Promo text starts with OB ALT the result is SAB 001 if not CRN 001.

2) If the company Omen has the Bank Name empty and the Promo text starts with P BEN the result is SAB 002 if not CRN 002.

3) If the company Hey has the Bank Name empty and the Promo text starts with P ALT the result is SAB 003 if not CRN 003.

Once again thank you very much for your help.

[dufoq3 · Accepted solution]

Hi @PMR18, your request doesn't make sense to me but you can inspire by this:

Result

let
    TableData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRClBwcvVzdHRUitWJVoLxnZycwHwXVzewkKNPiLOzM5ISJycXFxcEHyjv6uqKJO/lDea4e3hC5A9P9EdSDebFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bank = _t, Promo = _t]),
    TableHelper = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClBwcvVT0lEKdnQyMFCK1QGJOPqEQEUMYSKHJ/pDhYyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    ReplaceBlankToNull = Table.TransformColumns(TableData, {}, each if Text.Trim(_) = "" then null else _),
    Ad_PromoHelper = Table.AddColumn(ReplaceBlankToNull, "PromoHelper", each Text.Start([Promo], 5), type text),
    MergedQueries = Table.NestedJoin(Ad_PromoHelper, {"PromoHelper"}, TableHelper, {"A"}, "TableHelper", JoinKind.LeftOuter),
    ExpandedTableHelper = Table.ExpandTableColumn(MergedQueries, "TableHelper", {"B"}, {"B"}),
    Ad_FinalColumn = Table.AddColumn(ExpandedTableHelper, "FinalColumn", each if [Bank] = null then [B] else null, type text)
in
    Ad_FinalColumn