Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Iterative search

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 ValueReturn Value
JavaOracle Java
OfficeMicrosoft Office

 

Data Table

FindingSoftware Package
The version of Java running on the Machine is out of dateOracle Java
Office 4.3 is running on the machineMicrosoft 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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

I went through similar transition from Excel to PBI 2 yrs ago and came to 2 points till today,

  1. there are at least 2 major tasks: DAX and M languages, either is indispensable;
  2. DAX funcs have nearly nothing to do with Excel funcs despite same names.

 

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"

Screenshot 2021-10-07 141330.png


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!

Anonymous
Not applicable

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

 

Anonymous
Not applicable

Hi thanks for the quick response, I will get on to trying that and let you know how I got on

Tahreem24
Super User
Super User

@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:

 
New Column = IF(SEARCH(RELATED(SearchTable[Return]),Data[Software Package],1,0)>0,RELATED(SearchTable[Return]),"Not Found")
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.