Wildcard Price Lookup Solution

Analysis of Problem

The task involves creating a lookup mechanism that considers potential wildcards (*) in multiple columns for determining the correct price from a price file based on the data in a product file within an ERP system.

Solution Overview

The main solution involves:

1. Setting up a method to handle wildcard logic when searching for matches.
2. Ensuring that the correct price is chosen based on the specific combination of category, sub-category, product group, and product.

DAX vs. Power Query

Depending on the approach, both DAX (used in Power BI and Excel) and Power Query (M language) can be used to solve this problem.

Using Power Query (M Language)

1. Import Data: Load both the product file and price file into Power Query.
2. Expand Price File: Handle wildcard logic by transforming the price data to cover all wildcard combinations.
3. Merge Tables: Join the product file with the expanded price file to lookup the prices.

Step-by-Step in Power Query

// Step 1: Load your Product and Price tables
let
    ProductTable = ... // Your product file data source
    PriceTable = ...   // Your price file data source

// Step 2: Expand each wildcard combination in the PriceTable

ExpandedPriceTable = Table.TransformColumns(PriceTable, {{"Category", each {[_, '*']}}}) & 
                     Table.TransformColumns(PriceTable, {{"Sub-Category", each {[_, '*']}}}) & 
                     Table.TransformColumns(PriceTable, {{"Product Group", each {[_, '*']}}}) & 
                     Table.TransformColumns(PriceTable, {{"Product", each {[_, '*']}}})

in
    ExpandedPriceTable

// Step 3: Merge the ProductTable with ExpandedPriceTable to lookup prices
MergedTable = Table.NestedJoin(
    ProductTable, 
    {"Category", "Sub-Category", "Product Group", "Product"}, 
    ExpandedPriceTable, 
    {"Category", "Sub-Category", "Product Group", "Product"}, 
    "Price",
    JoinKind.LeftOuter
)
in
    MergedTable

// Step 4: Choose the best price by filtering merged results based on priority columns
FinalPrices = ... // Logic to pick the best match excluding '*'

Using DAX

In DAX, the primary challenge is to create a search mechanism that filters rows based on the hierarchical priority. By using calculated columns and measures, the task can be achieved.

Step-by-Step in DAX

1. Create a Relationship: Assume both tables are in the same data model with appropriate relationships.
2. Use Calculated Columns: To prioritize price selection based on non-wildcard matches.

DAX Formula

Create a calculated column in your Product table to find matching prices:

PriceLookup =
VAR CurrentCategory = ProductTable[Category]
VAR CurrentSubCategory = ProductTable[Sub-Category]
VAR CurrentProductGroup = ProductTable[Product Group]
VAR CurrentProduct = ProductTable[Product]
RETURN 
    CALCULATE(
        MIN(PriceTable[Price]),
        FILTER(
            PriceTable,
            (PriceTable[Category] = CurrentCategory || PriceTable[Category] = "*") &&
            (PriceTable[Sub-Category] = CurrentSubCategory || PriceTable[Sub-Category] = "*") &&
            (PriceTable[Product Group] = CurrentProductGroup || PriceTable[Product Group] = "*") &&
            (PriceTable[Product] = CurrentProduct || PriceTable[Product] = "*")
        )
    )

Final Touches

• Validation: Ensure the solution is scalable and performs well with large data sets.
• Optimization: Focus on performance in DAX using variables and avoiding overuse of calculated columns if not necessary.

Conclusion

Handling wildcard logic in a lookup scenario can be complex but manageable within both Power Query and DAX. The method involves expanding the wildcard possibilities and carefully selecting the matching records based on criteria hierarchy.

For further detailed learning, please refer to the Enterprise DNA Platform's courses and resources.