How to filter a table based on the length of a value.

[fsadiq]

Hi All,

I need support on filtering values based on the value's length. First,  I have a table that contain alpha numeric characters with different value lengths, I would like to filter and display only the one's with 4 letters. See table below.

Project Structure: Description                    

CS.1176

CS.1176.BD14

CS.1176.BD14.03

CS.1176.BD14.03.05

CS.1176.BD14.03.05.001

CS.1176.BD14.10

CS.1178

CS.1179

CS.1180

2. If also want to know if its possible that when you do a filter and select the values(4 letters) as requested in the first question, it displays only the associated surfix values only. Example,

If i do a filter visual to list the reduced 4 letters, i.e CS.1176 as shown in the above table, and i select it, it should display only values that starts with the prefix CS.1176, ie. CS.1176.BD14, CS.1176.BD14.03.05 etc.

Thanks, in Anticipation.

[Ahmedx · Accepted solution]

you can create a calculated column in power query and count the number of letters and then you can already filter by the number of letters.

Text.Length(
Text.Select(
    [DATA],
         {"A".."z"}))

[fsadiq · Accepted solution]

Hi @Ahmedx , Many thanks. It actually did the magic. I only also modified it to filter out the values above 7 characters by using the below while following your steps.

Text.Length([Project ID]) >7

Now to the 2nd question. I want a situation where by If i do a filter visual to list the reduced 7 letters, i.e CS.1176 as shown in the above table, and i select it, it should display only values that starts with the prefix CS.1176, ie. CS.1176.BD14, CS.1176.BD14.03.05 etc. as depicted in the below image.

Thanks.

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RiEQYWSCNqQAAS07t?e=gvl7gf

[Ahmedx · Accepted solution]

you can create a calculated column in power query and count the number of letters and then you can already filter by the number of letters.

Text.Length(
Text.Select(
    [DATA],
         {"A".."z"}))