Validating Columns Against Other Column in Dataset

[gsk96]

Validating Pizza IDs Against Pizza Names in Dataset

Hello everyone,

I’m working with a dataset where I need to ensure that the Pizza ID corresponds correctly to the Pizza Name. Specifically, I have multiple entries for the same pizza type with different IDs.

For example, the Pizza IDs for "The Barbecue Chicken Pizza" are:

• bbq_ckn_s
• bbq_ckn_m
• bbq_ckn_l

I want to check whether these IDs are correctly associated with the Pizza Name "The Barbecue Chicken Pizza."

Could anyone suggest an efficient way to validate this within my dataset?

Here's the file, Pizza sales 

[gsk96]

Hi @powerbiexpert22 and @DataNinja777 I have sucessfully solved it and it is working right.

Here's how I did it

ComparisonResult =

IF(

    LEFT([pizza_name_id], 3) = MID([pizza_name], 5, 3)

    || (SEARCH("bbq", [pizza_name_id], 1, 0) > 0 && SEARCH("barbecue", [pizza_name], 1, 0) > 0),

    TRUE,

    FALSE

)

[DataNinja777 · Accepted solution]

Hi @gsk96 ,

Does your company's ERP system have a product data table (dimension table) that is normalized, so the pizza size is not included with each product entry? Or is the pizza size part of the product code? Since your pizza sales table is a fact table, you only need the Pizza ID in it. I recommend taking the pizza names from the product table, using the Pizza ID as a lookup key.

Best regards,

[powerbiexpert22]

Hi @gsk96 ,

you can create calculated column in power bi similar to shown below and filter your results to "True". please see below file for your reference

https://drive.google.com/file/d/1wpnoW7LMQdKMsqe9fvpU7mTbyaNNHSEP/view?usp=drive_link

pizzacompar =

SWITCH(

    TRUE(),

    LEFT(pizza_sales[pizza_name_id], 7) = "bbq_ckn" && pizza_sales[pizza_name]= "The Barbecue Chicken Pizza", "True",

    LEFT(pizza_sales[pizza_name_id], 7) = "ital_supr" && pizza_sales[pizza_name] = "The Italian Supreme Pizza", "True",

    "False"

)

[gsk96]

Hi @powerbiexpert22 It is a tedious job as there are 32 pizza names and 96 pizza ids