cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
annade22
New Member

Filter query

Hi,

I hope this is a simple question to answer. I am new to the power query so I need all help I can get. I need to filter out all project id:s ending on -**-**. In the example below I only want to keep e.g. 30000002 and 30000002-01, but I don't want to keep 30000002-01-01. I have tried to use the option "not ends with" but I don't get it to work.

 

Appreciate all your help

 

Anna

 

2019-08-09 09_16_31-Projekt 2019–Power Query Editor.png

2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

if the project_id is always in the same pattern as the sample data ie, 8 chars or 8-2 chars or 8-2-2 chars then

Add a column like this

= Table.AddColumn(#"Added Custom", "LastPos", each if Text.PositionOf([ProjectID], "-", Occurrence.Last) = 12 then 1 else 0)

That'll give a 1 in a new column for the 8-2-2 pattern.

Then you filter the rows (from the dropdown in column LastPos) to keep the 0 rows

View solution in original post

Mariusz
Community Champion
Community Champion

Hi @annade22 

 

You can add Custom column like below and later liter it > 3

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @annade22 

 

You can add Custom column like below and later liter it > 3

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Mariusz
Community Champion
Community Champion

Hi @annade22 

 

Sorry this should be < 3.

 


@Mariusz wrote:

Hi @annade22 

 

You can add Custom column like below and later liter it > 3

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 




HotChilli
Super User
Super User

if the project_id is always in the same pattern as the sample data ie, 8 chars or 8-2 chars or 8-2-2 chars then

Add a column like this

= Table.AddColumn(#"Added Custom", "LastPos", each if Text.PositionOf([ProjectID], "-", Occurrence.Last) = 12 then 1 else 0)

That'll give a 1 in a new column for the 8-2-2 pattern.

Then you filter the rows (from the dropdown in column LastPos) to keep the 0 rows

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!