Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |