Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have been moving from Excel to Power BI for a few months and have been migrating tools, ditching the old way of working to use the power of Power BI.
In one of the tools I wrote a VBA function which did an iterative lookup to search of values in one table which checked inside the contents of a cell in another sheet and then returned a value from the lookup table.
Search Table
Lookup Value | Return Value |
Java | Oracle Java |
Office | Microsoft Office |
Data Table
Finding | Software Package |
The version of Java running on the Machine is out of date | Oracle Java |
Office 4.3 is running on the machine | Microsoft Office |
In Excel my function the data table did a for loop until it had a match then returned the result into the Software Package, it worked by taking the "Finding value" and then doing a loop by increasing the row count in the search table until a match was found.
It wasn't elegant but it worked better than using a huge nested if statement. I have searched and perhaps not using the correct words to describe what I am trying to do, but is this possible?
I would still prefer to use a table to add further search strings to if that is possible, I know it isnt fashionable to try and mimic Excel but I'm trying to make things easier for everyone involved (mainly me ;))
Thanks
Solved! Go to Solution.
Firstly thanks for your help but as i cannot link the tables it didnt work.
What I found was a solution here - https://community.powerbi.com/t5/Desktop/Wildcard-matching-amp-returning-a-value/m-p/470276 which uses the minx function, no idea how but its worked for me
I went through similar transition from Excel to PBI 2 yrs ago and came to 2 points till today,
As to your question, I'll solve it in PQ given that PQ is designed to play a role in ETL.
let
Search = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kosS1TSUfIvSkzOSVUA82J1opX809Iyk1OBEr6ZyUX5xflpJQpQodhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lookup Value" = _t, #"Return Value" = _t]),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVShLLSrOzM9TyE9T8EosS1QoKs3Ly8xLVwAKlQClfROTMzLzUhUyixXyS0tAqlISS1KVYnWilfzT0jKTUxVM9IxBsmj6ciH6lGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Finding = _t]),
#"Added Custom" = Table.AddColumn(Source, "Software Package", each List.Accumulate(Table.ToRecords(Search), [Finding], (s,c) => if Text.Contains(s, c[Lookup Value], Comparer.OrdinalIgnoreCase) then c[Return Value] else s))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Firstly thanks for your help but as i cannot link the tables it didnt work.
What I found was a solution here - https://community.powerbi.com/t5/Desktop/Wildcard-matching-amp-returning-a-value/m-p/470276 which uses the minx function, no idea how but its worked for me
Hi thanks for the quick response, I will get on to trying that and let you know how I got on
@Anonymous You can use SEARCH function like below. You can tweak this calculated column DAX as per your need.
First need to create a relationship between both these table using common column i.e. Software Package and Return Value. Then create a DAX column like below:
I realised an issue with my description
in the data table there is no data in the software package, the aim is to populate that field with the lookup data. This means I cannot create a relationship between the two as there is no unique field
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |