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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors