Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
THENNA_41
Post Partisan
Post Partisan

Conditions Not working In power Query

I have attached Sample Data Below i am trying to new column in power bi based few conditions .

 

if Itemcode start with ME ,FA,FM,FC,MK new column value should  0. Now if  one more condition if Itemcode start with "ME" and Product Category ="M0101 - RRP HTP Sticks" and  Markets ="CA04" &  Cigarettes Per Pack  =180,  Cigarettes Per Outer (*) = 180 if above condition match new column value should be 2.

 

Sample Data:

Item Code - Copy.2        Product Category                   Markets    Cigarettes Per Pack  Cigarettes Per Outer (*)

ME101.01                    F0101- RRP HTP Sticks               ID04                   20                                 20
ME141.01                     M0101 - RRP HTP Sticks           CA04                 180                               180
ME171.01                     M0101 - RRP HTP Sticks          CA04                  180                                180
FA101.01                      FA0101 - RRP HTP Sticks          CA04                   80                                  80
FM101.01                     FM101- RRP HTP Sticks            ID04                   20                                   20
FC101.01                      FC101- RRP HTP Sticks             ID04                    20                                  20
MK101.01                     MK101- RRP HTP Sticks           ID04                    20                                  20
DE101.01                     MK101- RRP HTP Sticks             ID04                   20                                   20

 

My Query :


= Table.AddColumn(
#"Renamed Columns9",
"prod Cat",
each
if Text.StartsWith([#"Item Code - Copy.2"], "FA") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FM") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FC") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "MK") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "ME") then 0
else if [#"Product Category (Product Information)"] = "M0101 - RRP HTP Sticks" and
[#"Markets"] = "CA04" and
[#"Cigarettes Per Pack (Product Information)"] = 180 and
[#"Cigarettes Per Outer (*)"] = 180 then 2
else 1
)

 

Tired this code but i am not able to view 2 value in the column 

 

Expected Output:

 

Item Code - Copy.2        Product Category                   Markets    Cigarettes Per Pack  Cigarettes Per Outer (*)     prod Cat

ME101.01                    F0101- RRP HTP Sticks               ID04                   20                                 20                           0
ME141.01                     M0101 - RRP HTP Sticks           CA04                 180                               180                           2
ME171.01                     M0101 - RRP HTP Sticks          CA04                  180                                180                           2
FA101.01                      FA0101 - RRP HTP Sticks          CA04                   80                                  80                            0
FM101.01                     FM101- RRP HTP Sticks            ID04                   20                                   20                            0
FC101.01                      FC101- RRP HTP Sticks             ID04                    20                                  20                            0
MK101.01                     MK101- RRP HTP Sticks           ID04                    20                                  20                             0
DE101.01                     MK101- RRP HTP Sticks             ID04                   20                                   20                             1

 

 

Please any one help .. thanks in advance 

 

 

1 ACCEPTED SOLUTION

Then I suspect the value 180 which was passed as a number, is actually a text simply turn that into a sting as illustated below.

Table.AddColumn(
#"Renamed Columns9",
"prod Cat",
each
if ([#"Product Category (Product Information)"] = "M0101 - RRP HTP Sticks" and
[#"Markets"] = "CA04" and
[#"Cigarettes Per Pack (Product Information)"] = "180" and
[#"Cigarettes Per Outer (*)"] = "180") then 2
else if Text.StartsWith([#"Item Code - Copy.2"], "FA") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FM") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FC") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "MK") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "ME") then 0
else 1
)

 I hope this is helpful

View solution in original post

4 REPLIES 4
THENNA_41
Post Partisan
Post Partisan

@m_dekorte  Thank you so much now its working .. 

m_dekorte
Super User
Super User

Hi @THENNA_41,

 

Apply precedence ordering, the multi condition statement now has a set of parentheses

 

Table.AddColumn(
#"Renamed Columns9",
"prod Cat",
each
if ([#"Product Category (Product Information)"] = "M0101 - RRP HTP Sticks" and
[#"Markets"] = "CA04" and
[#"Cigarettes Per Pack (Product Information)"] = 180 and
[#"Cigarettes Per Outer (*)"] = 180) then 2
else if Text.StartsWith([#"Item Code - Copy.2"], "FA") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FM") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FC") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "MK") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "ME") then 0
else 1
)

 

 I hope this is helpful

@m_dekorte  tired above but still i could see 0 and 1 only .. 

Then I suspect the value 180 which was passed as a number, is actually a text simply turn that into a sting as illustated below.

Table.AddColumn(
#"Renamed Columns9",
"prod Cat",
each
if ([#"Product Category (Product Information)"] = "M0101 - RRP HTP Sticks" and
[#"Markets"] = "CA04" and
[#"Cigarettes Per Pack (Product Information)"] = "180" and
[#"Cigarettes Per Outer (*)"] = "180") then 2
else if Text.StartsWith([#"Item Code - Copy.2"], "FA") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FM") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "FC") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "MK") then 0
else if Text.StartsWith([#"Item Code - Copy.2"], "ME") then 0
else 1
)

 I hope this is helpful

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors