Forum Discussion

gsk96's avatar
gsk96
New Member
1 year ago
Solved

Validating Columns Against Other Column in Dataset

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 

 

 

  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    powerbiexpert22
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    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's avatar
    gsk96
    New Member
    1 year ago

    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
    )