robert has attended:
Power BI Modelling, Visualisation and Publishing course
Power BI set Variable
Is it possible to create an excel sheet with criteria in a column that is a concatenation of five columns of data next to it, then bring that into PowerBI as a variable to allocate each row a sales channel?
For example;
Retailer_Number 56***
Order_Type 00
Customer_Type **
RFO1 ******
RFO2 ******
Reference 56***00**************
Sales_Efficiency_Channel Retail Agency
Retailer_Number *****
Order_Type 08
Customer_Type 72
RFO1 205675
RFO2 ******
Reference *****0872205675******
Sales_Efficiency_Channel Fleet Agency
Retailer_Number *****
Order_Type 03
Customer_Type 79
RFO1 ******
RFO2 ******
Reference *****0379************
Sales_Efficiency_Channel Fleet Agency Corporate
Where the Reference column is the concatenated result, the * is wildcards, but match to a sales file to bring back each lines' sales channel
RE: Power BI set Variable
Hi Robert,
Thank you for the forum question.
Yes, what you're describing is absolutely possible in Power BI, and it's a common approach for mapping or categorizing data using reference tables. Here's how you can achieve this step-by-step:
Step 1: Create the Reference Table in Excel
In Excel, create a table with the following structure:
| Retailer_Number | Order_Type | Customer_Type | RFO1 | RFO2 | Reference | Sales_Efficiency_Channel |
|------------------|-------------|----------------|--------|--------|------------------------|---------------------------|
| 56*** | 00 | ** | ****** | ****** | 56***00************** | Retail Agency |
| ***** | 08 | 72 | 205675 | ****** | *****0872205675****** | Fleet Agency |
| ***** | 03 | 79 | ****** | ****** | *****0379************ | Fleet Agency Corporate |
- The `Reference` column is a concatenation of the other columns, using wildcards (`*`) to allow flexible matching.
- Save this as `SalesChannelMapping.xlsx`.
Step 2: Load Data into Power BI
1. Load your sales data and the reference mapping table into Power BI.
2. In Power Query, create a new column in your sales data that concatenates the same fields as in the reference table to form a `Reference` key.
powerquery
= Text.PadStart([Retailer_Number], 5, "0") &
Text.PadStart([Order_Type], 2, "0") &
Text.PadStart([Customer_Type], 2, "0") &
Text.PadStart([RFO1], 6, "0") &
Text.PadStart([RFO2], 6, "0")
Adjust padding as needed based on your data format.
Step 3: Perform the Matching
Since Power BI doesn't support wildcard joins directly, you have two main options:
Option A: Use Power Query M Code for Pattern Matching
You can write a custom function in Power Query to simulate wildcard matching by comparing each row in the sales data to each row in the reference table.
Option B: Use DAX with LOOKUP or SWITCH Logic
If the number of patterns is manageable, you can use a `SWITCH(TRUE(), ...)` DAX formula to match patterns using `SEARCH` or `CONTAINSSTRING`.
Example:
DAX
Sales_Channel =
SWITCH(TRUE(),
CONTAINSSTRING([Reference], "56") && CONTAINSSTRING([Reference], "00"), "Retail Agency",
CONTAINSSTRING([Reference], "08") && CONTAINSSTRING([Reference], "72") && CONTAINSSTRING([Reference], "205675"), "Fleet Agency",
)
Step 4: Use the Result
Once matched, you can use the `Sales_Efficiency_Channel` in your reports, visuals, and filters.
---
Would you like help writing the Power Query function for wildcard matching, or would you prefer a DAX-based approach?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
