cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeBIe
Helper IV
Helper IV

Extracting specific data format from column

Hi all!

 

I had an earlier request to extract license plates (1-ABC-123) from a column, which worked fine for me. All license plates with that format are being extracted and shown in my dashboard. The problem I am facing at the moment is that I have a new dataset where the format isn't always the same for a license plate. For example:

 

Data
1-ABC-123
1ABC123
1-1-2021 t/m 31-1-2021 1DEF456
CAR FUEL 1GHI789
1JKL123 LEASE

 

In some way, I need to be able to extract the license plates from the dataset which do not have the standard format "1-ABC-123".

The conditions are that the length should be 7 characters. A first character is a number (1), then 3 text characters, the last 3 are numbers (1ABC123).

The complexity here is that the license plates are not always filled in first in the data field, otherwise, I could extract the first 7 characters. Then do a check if the first character is a number, the next three are characters, and the last three are numbers. If that condition would be true then I can be almost 100% sure it would be a license plate. I've been breaking my mind to find a solution for this, while it sounds easy, every time I start trying things I end up failing.

 

Your help is much appreciated

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @DeBIe 

 

Download sample PBIX

 

Give this Power Query code a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtR1dHLWNTQyVorVAfKAHDhb11DXyMDIUKFEP1fBGM4zdHF1MzE1AytxdgxScAt19VEwdPfwNLewhOjz8vYBmqHg4+oY7KoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Data], " ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Select([Custom], each Text.Length(Text.Trim(_)) > 6)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Combine(List.Select(List.Select([Custom.1], each Text.PositionOfAny(Text.Upper(_), {"0".."9"})>-1 ), each Text.PositionOfAny(Text.Upper(_), {"A".."Z"})>-1 ))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if Text.PositionOf([Custom.2], "-") <> 1 then Text.Insert(Text.Insert(Text.From([Custom.2]), 4 , "-"),1,"-") else [Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Custom.2"})
    
in
    #"Removed Columns"

Screenshot 2021-03-25 205132.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
DeBIe
Helper IV
Helper IV

@PhilipTreacy 

 

Thank you very much for your help. I have not been able to test it myself yet because of other business issues that came around. Will give it a try asap and let you know then!

@DeBIe 

OK, let me know if you have any issues with it.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy 

 

I am happy to say that it worked for me! I had to do some small modifications to extract all license plates from the data. But you helped me perfectly to realize that!

 

PhilipTreacy
Super User III
Super User III

Hi @DeBIe 

 

Download sample PBIX

 

Give this Power Query code a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtR1dHLWNTQyVorVAfKAHDhb11DXyMDIUKFEP1fBGM4zdHF1MzE1AytxdgxScAt19VEwdPfwNLewhOjz8vYBmqHg4+oY7KoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Data], " ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Select([Custom], each Text.Length(Text.Trim(_)) > 6)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Combine(List.Select(List.Select([Custom.1], each Text.PositionOfAny(Text.Upper(_), {"0".."9"})>-1 ), each Text.PositionOfAny(Text.Upper(_), {"A".."Z"})>-1 ))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if Text.PositionOf([Custom.2], "-") <> 1 then Text.Insert(Text.Insert(Text.From([Custom.2]), 4 , "-"),1,"-") else [Custom.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Custom.2"})
    
in
    #"Removed Columns"

Screenshot 2021-03-25 205132.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors