Wildcard matching and returning lookup values

[Anonymous]

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 Number	Geo	Total Billed
7059927123	AMER	$400
7059867738	EMEA	$600

CS Rep: 

Created By	SAP Order Number
A	7059926465 & 7059927123
B	7061344030
A	7061344031
C	7061344032
A	7059867738; 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 Number	Geo	Total Billed	Agent
7059927123	AMER	$400	A
7059867738	EMEA	$600	A

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 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[hthienel]

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]

Hi, @Anonymous 

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

BCN:

CS Rep:

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:

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]

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 

[v-alq-msft]

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]

@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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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 

[Ashish_Mathur]

Hi,

That step was manually written.  Please read this.

Hope this helps.

[Anonymous]

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: 

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 

[Ashish_Mathur]

Hi,

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

[Anonymous]

@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

[Ashish_Mathur]

Hi,

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

[Anonymous · Accepted solution]

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 

[Ashish_Mathur]

Thank you for sharing your knowledge.

[Greg_Deckler]

@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")

[Anonymous]

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")

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

@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

[FrankAT]

Hi @Anonymous 

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

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