Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

power bi set variable

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Power BI set Variable

Power BI set Variable

ResolvedVersion 365
Edited on Thu 5 Jun 2025, 14:43

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

Thu 12 Jun 2025: Automatically marked as resolved.

 

Training courses

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Power BI tip:

Stay Updated and Engage with the Community

Power BI is continually evolving, with new features and updates being released regularly. Stay informed about the latest enhancements by regularly checking the Power BI blog and community forums. Engaging with the Power BI community provides opportunities to learn from others, share your experiences, and get insights into best practices.

View all Power BI hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.