March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
10 | |
7 |
User | Count |
---|---|
42 | |
25 | |
16 | |
16 | |
11 |