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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Wildcard matching and returning lookup values

Hi, 

I have two tables: one table is for orders(called BCN) and the other is called CS Rep to look up agent names. 

BCN: 

 

Order NumberGeo Total Billed
7059927123AMER$400
7059867738EMEA$600

CS Rep: 

 

Created BySAP Order Number
A7059926465 & 7059927123
B7061344030
A7061344031
C7061344032
A7059867738; 7059867420


What's the most efficient way to do wildcard matching and return created by column to orders table? Ideal output should be like: 

 

Order NumberGeo Total BilledAgent
7059927123AMER$400A
7059867738EMEA$600A

Since order number 7059927123 is part of the string of 7059926465 & 7059927123, so the new Agent column should return Agent A. Same as 7059867738, it should return A as the agent. 

There's no certain logic/delimeter for the SAP order number column. Its value could contain more than 2 order numbers as below: 

 

7059153390?7059153622,7059153531
7058866248 7058866249
7058884953 &  7058884954
7058891703, 7250568299, 7058895766

 

I tried MINX function as below but it's not returning the correct values. Instead it returns blank for both records: 

 

 

Agent = MINX(FILTER('CS Rep',SEARCH('CS Rep'[SAP Order Number],BCN[Order Number],1,0)>0),'CS Rep'[Created By])

 

 

Can someone please help? Thanks in advance! Sample pbix 

Best,
Dennis 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Anonymous
Not applicable

Hi @Ashish_Mathur

I had to slightly modify your query as below: 

    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "SAP Order Number", Splitter.SplitTextByAnyDelimiter({"&",".",":",",", " ","?",";","/","*","・"}, QuoteStyle.Csv), {"SAP Order Number.1", "SAP Order Number.2", "SAP Order Number.3", "SAP Order Number.4", "SAP Order Number.5"}),

Basically I had to denote how many columns I'd like to split and then it worked. 

Thanks for your guidance. I will mark your answer as the solution. 

Regards,

Dennis 

View solution in original post

21 REPLIES 21
hthienel
Regular Visitor

Hi,

 

Have you tried creating a list of all order numbers in BCN (as a separate list), then splitting the "search column" SAP order number in CS by all of those values using the Splitter.SplitTextByAnyDelimiter function and counting the resulting rows for a match? If that list has >1 rows then the split worked and an order number was found.

 

Then you could use List.Accumulate to iterate over the the 2 lists (1 - order number from BCN, 2 - results from the split, which will include all valid order numbers contained in CS and BCN) and return the matched items. 

 

A brilliant example for the text matching can be found here: https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-que...

 

Regards,

Helmut.

 

v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

BCN:

c1.png

 

CS Rep:

c2.png

 

You may create a calculated column or a meausre as below.

Calculated column:

Column = 
CONCATENATEX(
    CALCULATETABLE(
        DISTINCT('CS Rep'[Created By]),
        FILTER(
            ALL('CS Rep'),
            CONTAINSSTRING([SAP Order Number],EARLIER(BCN[Order Number]))
        )
    ),
    [Created By],
    " "           
)

 

Measure:

Measure = 
CONCATENATEX(
    CALCULATETABLE(
        DISTINCT('CS Rep'[Created By]),
        FILTER(
            ALL('CS Rep'),
            CONTAINSSTRING([SAP Order Number],SELECTEDVALUE(BCN[Order Number]))
        )
    ),
    [Created By],
    " "           
)

 

Result:

c3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Anonymous
Not applicable

Hi Allan @v-alq-msft ,

Thanks for the proposed solution. It works pretty well in the sample data set, however it took forever for me to run. My raw data table has over 2 million rows and Earlier function will scan each of the row, correct? What's your suggestion in this case? 

Thanks

Dennis 

Hi, @Anonymous 

 

You may use a variable to keep the 'Order Number' as below.

Column = 
var _num = BCN[Order Number]
return
CONCATENATEX(
    CALCULATETABLE(
        DISTINCT('CS Rep'[Created By]),
        FILTER(
            ALL('CS Rep'),
            CONTAINSSTRING([SAP Order Number],_num)
        )
    ),
    [Created By],
    " "           
)

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@v-alq-msft Hi Allan, 

For some reason it's still taking forever to update the formula. I honestly never encountered this issue before. It either gave me an error message or updated pretty soon. But using this formula - my PBI just shows "Working on it" window and never refreshes. 

Would you please advise? 

Thanks,
Dennis 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

Would you guide me through how can the "Custom1" step? How would I split the text by any delimeter from the following strings? I tried to replicate the same in my power editor but didn't get the same result. 

Thanks,

Dennis 

 

Hi,

That step was manually written.  Please read this.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur , 

After applying your custom step, I only got one split column as the screenshot below. Unlike your sample pbix file has 4 split columns: 

Screen Shot 2020-09-15 at 9.53.34 PM.png

In my real dataset, the SAP order number could contain multiple order numbers using different delimeters. The sample pbix file I provided might simplify my issue. For example, the SAP order numbers could be: 

*7060741668
7058726811 & 7058726812*7058866248
7058869199 / 7058869362, 7058866249
7059153390?7059153622,7059153531

Anyway we can solve this problem? I have an updated file with complete list of orders that describe my problem. 

Thanks,
Dennis

 

File Link: Sample file.pbix 

Hi,

Share Book3.xlsx and Book4.xlsx as well.  Also, in another workbook, please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur please use the links below for book3 and book4. Expected result should be one SAP order number per created agent per row. 

book3.xlsx

book4.xlsx

Hi,

I am facing the the same problem as you.  Do not know why that is happening. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

I had to slightly modify your query as below: 

    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "SAP Order Number", Splitter.SplitTextByAnyDelimiter({"&",".",":",",", " ","?",";","/","*","・"}, QuoteStyle.Csv), {"SAP Order Number.1", "SAP Order Number.2", "SAP Order Number.3", "SAP Order Number.4", "SAP Order Number.5"}),

Basically I had to denote how many columns I'd like to split and then it worked. 

Thanks for your guidance. I will mark your answer as the solution. 

Regards,

Dennis 

Thank you for sharing your knowledge.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@Anonymous I'll try to look at the file. In general, you can potentially use fuzzy matching in Power Query Merge query. I also created a DAX fuzzy matching algorithm. https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/td-p/1352914

 

But, sometimes you can get there with something as simple as:

IF(SEARCH(<find>,<within>,,0)>0,"Match","No Match")

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks @Greg_Deckler

 

Would it be possible to use this formula to do lookup value instead of returning Match/No Match? 

IF(SEARCH(<find>,<within>,,0)>0,"Match","No Match")

 

@Anonymous Oh yeah, you could put any calculation in there really.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler any suggestion on how exactly I should use that formula to solve my question? Let me know if you get a chance to look through my sample pbix file. Appreicate your help! 

@Anonymous - OK, I mocked something up for you using columns, could also do measures. Table (4a) is what you want. Uses Table (4). PBIX is attached below sig.

Order = 
    VAR __SearchText = [SAP Order Number]
    VAR __Table = 
        ADDCOLUMNS(
            GENERATE(
                'Table (4)',
                'Table (4a)'
            ),
            "Found",IF(SEARCH([Order Number],__SearchText,,0)>0,1,0)
        )
    VAR __Value = MAXX(FILTER(__Table,[Found]=1),[Order Number])
RETURN
    __Value


Amount = 
    VAR __SearchText = [SAP Order Number]
    VAR __Table = 
        ADDCOLUMNS(
            GENERATE(
                'Table (4)',
                'Table (4a)'
            ),
            "Found",IF(SEARCH([Order Number],__SearchText,,0)>0,1,0)
        )
    VAR __Value = MAXX(FILTER(__Table,[Found]=1),[Total Billed])
RETURN
    __Value

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
FrankAT
Community Champion
Community Champion

Hi @Anonymous 

  1. Transform your data in Power Query.
  2. Created a relationship between BCN and CS Rep table.
  3. See attached pbix file.

10-09-_2020_23-20-13.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.