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

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Alexjaco2
New Member

Removing 3 digit numbers from a list

Hi All, 

I have a list of numbers (stored as texts) that are semi colon sepearated and I need specific 3 digit values removed. Is there any Power Query that can do this? Here is what I have and what I need. 

HaveWant
9083290832
9921399213
9921399213
9921399213
9921499214
9921499214
636 90750 ; 771 9047190750 ; 90471
9921399213
9921499214
914 9079190791
9921499214
636 90674 ; 771 9047190674 ; 90471
9921499214
9921499214
301 80050 ; 301 80061 ; 301 82607 ; 780 Q301480050 ; 80061 ; 82607 ; Q3014
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@Alexjaco2 Maybe this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc2xDYAwDATAVazUFO/Y2ImYgjrK/mvgIGiiQHey/P+tpYoiOfUtVDPLr3SSiVGF76CD3Dmszt851vFdedlirsuWeVPAVIB787Hx62zw0VJAZxwi0S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Have = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Have", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(Text.Split([Have], " "), each Text.Length(_) <> 3)))
in
    #"Added Custom"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Alexjaco2
New Member

That works! Thanks!

Greg_Deckler
Community Champion
Community Champion

@Alexjaco2 Maybe this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc2xDYAwDATAVazUFO/Y2ImYgjrK/mvgIGiiQHey/P+tpYoiOfUtVDPLr3SSiVGF76CD3Dmszt851vFdedlirsuWeVPAVIB787Hx62zw0VJAZxwi0S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Have = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Have", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(Text.Split([Have], " "), each Text.Length(_) <> 3)))
in
    #"Added Custom"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.