Would like help using function containsstring or search function or ..?

[DeBIe]

Hi All,

Based on a big datasheet I need to be able to filter data that can be related to a car brand / model / license plate format.

I would like to add a conditional column to my datasheet which tells me if a specific column contains a car brand / model / license plate format. A simple true/false would suffice if the value has been found. I have stored all possible car brands and models in a separate excel file, which will be my keyword or search library. I can update this file to make sure that I always have the latest and newest models in the file. After creating this library, I would like to do a search/containsstring in my datasheet which will look into the library to see if it finds any matches. My first problem is that I am not able to point to my library file with all car brands and models because it is in another table. I need to be able to use the library table and column to use as my "FindText" parameter in the formula. This is the formula which doesn't work:

IsModel = CONTAINSSTRING(MasterData[Descriptions],Library[CarModels])

My second problem is that I also would like to add a column to see if a car license plate format has been found. In my datasheet, there are multiple which identify a license plate. For this, I have also expanded the library file with possible license plate formats. I would like to do the same containsstring or search function to see if I find any matches. I will try to provide you with some example data:

Masterdata table

Descriptions

27/10/2020 - 26/11/2020 1ABC245

: 1-RTE-987

01.02.20 - 29.02.20 1RTE987

01/2021 - 1SYZ213

01/2021 1-UDD-335

02.2020 1PTP162

01.02.20 - 29.02.2012. 2TKR986

01.02.20 - 29.02.201 1SAS947

: 1FGP720-31-01-2020

: 1JQX193-31-01-2020

: 1-TRT-125-31-01-2020

Library CarLicenseFormats

A car license plate will always start with the number 1 or 2, then 3 characters, then 3 numbers. In my masterdata descriptions datasheet, this can be delimited by "-". It is not a big problem if I won't be able to tackle everything. But my datasheet is huge, so it would already be amazing if I can filter most of them.

1A	2A	1-A	2-A
1B	2B	1-B	2-B
1C	2C	1-C	2-C
1D	2D	1-D	2-D
1E	2E	1-E	2-E
1F	2F	1-F	2-F
1G	2G	1-G	2-G
etc.	etc.	etc.	etc.

The result I would like to achieve would be

Descriptions	IsCarLicensePlate	IsCarModel
27/10/2020 - 26/11/2020 1ABC245	true	false
: 1-RTE-987	true	false
01.02.20 - 29.02.20 1RTE987	true	false
01/2021 - 1SYZ213	true	false
01/2021 1-UDD-335	true	false
02.2020 1PTP162	true	false
01.02.20 - 29.02.2012. 2TKR986	true	false
01.02.20 - 29.02.201 1SAS947	true	false
: 1FGP720-31-01-2020	true	false
: 1JQX193-31-01-2020	true	false
: 1-TRT-125-31-01-2020	true	false

Thank you very much for your time! Let me know if I need to provide you with additional info!

[HotChilli]

Have a look in the Advanced Editor or the Formula Bar (above the data in Power Query).  See if an extra "each" has been added before the (f) at the start.  It will be in blue text.  Just remove that "each"

[DeBIe]

Thank you very much! This worked for me and I am getting correct results so far!

I also had to filter out possible nulls in my library file, otherwise, I got the error that null cannot be converted to text.

[HotChilli · Accepted solution]

This is in Power Query,

Add a custom column and adapt this code for your table/column names:

(f) => List.AnyTrue(List.Transform(TableQ[#"Car Brand"], each Text.Contains(f[Descriptions], _))))

TableQ is the table with the brands in. Car Brand is the column 

I'm not the original writer of the code above so respect to whoever came up with it.

----

If you can get that working, the same pattern applies for the 2 other column searches.  You just need to add further custom columns with code that points at the right Table/column

Let me know how it goes

[DeBIe]

Hi @HotChilli ,

Thank you, I have added the custom column in my Descriptions table (Description column).

(f) => List.AnyTrue(List.Transform(Library[#"CarBrand"], each Text.Contains(f[Description], _)))

The result is a new column with the highlighted name 'function'.  I am actually not sure what this formula/function does for me? Hopefully, you can explain to me how it works.

Thanks!

[HotChilli]

There's a problem with the sample data provided (1st column is all true , 2nd column is all false)

Can you alter or provide some more sample data which shows 2 things:

Descriptions with no license plate, Descriptions with car model.

Could you also give us an example of the car brands data file (just a small sample please)?

[DeBIe]

Hi @HotChilli thank you for helping me.

My Masterdata table with the column Descriptions (can contain license plate / brand / model / or other)

27/10/2020 - 26/11/2020 1ABC245

: 1-RTE-987

01.02.20 - 29.02.20 1RTE987

01/2021 - 1SYZ213

01/2021 1-UDD-335

02.2020 1PTP162

01.02.20 - 29.02.2012. 2TKR986

01.02.20 - 29.02.201 1SAS947

: 1FGP720-31-01-2020

: 1JQX193-31-01-2020

: 1-TRT-125-31-01-2020

1-1-2021 Lease Mercedes

Audi fuel shell 11-1-2021

Lease Shell period february Opel

1/5/2021-Toyota-Fuel

Lease-Swift-Tank Shell 50

PC Inventory good

Booking App costs

Sales january overview

Purchase general march 2021

My Library looks like

PossibleCarLicensePlate	Car Brand	Car Model
1A	Mercedes	Swift
1B	Sprinter	Zafira
1C	Volkswagen	Vectra
1D	Fiat	Q7
1E	Opel	etc
1F	Ford
1G	Ferrari
2A	etc
2B
2C
2D
2E
2F
2G
1-A
1-B
1-C
1-D
1-E
1-F
1-G
2-A
2-B
2-C
2-D
2-E
2-F
2-G
etc

The expected result would be

Descriptions	IsLicensePlate	IsCarBrand	IsCarModel	IsCarRelated
27/10/2020 - 26/11/2020 1ABC245	True	False	False	True
: 1-RTE-987	True	False	False	True
01.02.20 - 29.02.20 1RTE987	True	False	False	True
01/2021 - 1SYZ213	True	False	False	True
01/2021 1-UDD-335	True	False	False	True
02.2020 1PTP162	True	False	False	True
01.02.20 - 29.02.2012. 2TKR986	True	False	False	True
01.02.20 - 29.02.201 1SAS947	True	False	False	True
: 1FGP720-31-01-2020	True	False	False	True
: 1JQX193-31-01-2020	True	False	False	True
: 1-TRT-125-31-01-2020	True	False	False	True
1-1-2021 Lease Mercedes	False	True	False	True
Audi fuel shell 11-1-2021	False	True	False	True
Lease Shell period february Opel	False	True	False	True
1/5/2021-Toyota-Fuel	False	True	False	True
Lease-Swift-Tank Shell 50	False	False	True	True
PC Inventory good	False	False	False	False
Booking App costs	False	False	False	False
Sales january overview	False	False	False	False
Purchase general march 2021	False	False	False	False