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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fsadiq
Frequent Visitor

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

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.

2 ACCEPTED SOLUTIONS
Ahmedx
Super User
Super User

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

Screen Capture #1199.png

View solution in original post

fsadiq
Frequent Visitor

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.

 

Capture.JPGCapture1.JPG

 

Thanks.

View solution in original post

3 REPLIES 3
fsadiq
Frequent Visitor

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.

 

Capture.JPGCapture1.JPG

 

Thanks.

Ahmedx
Super User
Super User

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

Screen Capture #1199.png

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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