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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NikhilPai
Frequent Visitor

If column contain numbers followed by - and again numbers then it should give OK else NOT OK

Hi,

 

Looking for some help in power query.

 

First : I have column that contain customer ID (something like 202307-52137). I want to create one more column to understand or check if these are having the correct set of numbers including the hyphen. Meaning, I should have 6 digits at the begining then hyphen and then again 5 digits. Any of these fails, it should throw me an error.

 

Second: I have one more column ID (something like 00018322) similar like above I want to know if this column contain 3 ZEROs at the begining followed by 5 digits. else should throw me an error.

 

Any help on this is highly appreciated. Many thanks.

 

regards,

Nikhil 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @NikhilPai ,

 

How about this? 🙂

tackytechtom_0-1711456688513.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYxLDsAgCETvwlpTPrV4F+MCe/87NGIjTTdkMu8NrQEjC2ouTKKQABGpCjP01IBYzjxPceLpcmLjfuXoVtz+VKI+4s3a/Cb2YbaZxbulaoXeHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CostumerID = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CostumerID", type text}, {"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CustomerIDCheck", each if Text.Length([CostumerID]) <> 12 then Error.Record("not exactly 12 characters")
else if Value.Is(
    Value.FromText(
        Text.Range([CostumerID], 0, 6)), 
        type number) 
= false then Error.Record("First 6 digits not a number") else if Value.Is(
    Value.FromText(
        Text.Range([CostumerID], 7, 5)), 
        type number) 
= false then Error.Record("Last 5 digits not a number") 
else if Text.Range([CostumerID], 6, 1) <> "-"
then Error.Record("7th character not a hyphen") 
else "ok"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IDCheck", each if Text.Length([ID]) <> 8 then Error.Record("not exactly 8 characters")
else if Text.Range([ID], 0, 3) <> "000" then Error.Record("First 3 digits not 000") else if Value.Is(
    Value.FromText(
        Text.Range([ID], 3, 5)), 
        type number) 
= false then Error.Record("Last 5 digits not a number")  
else "ok")
in
    #"Added Custom1"

 

Note, you might need to add a few more cases to the if clause, if they did not cover all your "ifs" and "buts". Also, the record discloses the reason for which check did not succeed.

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

Hi @NikhilPai, different approach here:

 

Result:

dufoq3_1-1711474637869.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYxLDsAgCETvwlpTPrV4F+MCe/87NGIjTTdkMu8NrQEjC2ouTKKQABGpCjP01IBYzjxPceLpcmLjfuXoVtz+VKI+4s3a/Cb2YbaZxbulaoXeHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, ID = _t]),
    Ad_CustomerIdCheck = Table.AddColumn(Source, "Customer ID Check", each 
        [ a = Text.Split(Text.Trim([CustomerID]), "-"),
          b = try List.AllTrue(List.Transform(a, (x)=> Number.From(x) is number) & {Text.Length(a{0}) = 6} & {Text.Length(a{1}) = 5}) otherwise false
        ][b], type logical),
    Ad_IdCheck = Table.AddColumn(Ad_CustomerIdCheck, "ID Check", each 
        if Text.Start([ID], 3) = "000" and Text.Length([ID]) = 8 and (try Number.From(Text.End([ID], 5)) is number otherwise false) then true else false, type logical)
in
    Ad_IdCheck

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

NikhilPai
Frequent Visitor

This works superbly. Many thanks for the help!! 

NikhilPai
Frequent Visitor

Hi Tom, 

 

Many thanks for your quick reply and working so detailedly on my request. I forgot to mention one thing here. both the columns (customer ID & ID) are in text format and I dont want to change it. Will the above query change in that case ?

 

Regards,

Nikhil 

Hi @NikhilPai ,

 

The query does not change the data type of your original columns 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

Hi @NikhilPai ,

 

How about this? 🙂

tackytechtom_0-1711456688513.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYxLDsAgCETvwlpTPrV4F+MCe/87NGIjTTdkMu8NrQEjC2ouTKKQABGpCjP01IBYzjxPceLpcmLjfuXoVtz+VKI+4s3a/Cb2YbaZxbulaoXeHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CostumerID = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CostumerID", type text}, {"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CustomerIDCheck", each if Text.Length([CostumerID]) <> 12 then Error.Record("not exactly 12 characters")
else if Value.Is(
    Value.FromText(
        Text.Range([CostumerID], 0, 6)), 
        type number) 
= false then Error.Record("First 6 digits not a number") else if Value.Is(
    Value.FromText(
        Text.Range([CostumerID], 7, 5)), 
        type number) 
= false then Error.Record("Last 5 digits not a number") 
else if Text.Range([CostumerID], 6, 1) <> "-"
then Error.Record("7th character not a hyphen") 
else "ok"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IDCheck", each if Text.Length([ID]) <> 8 then Error.Record("not exactly 8 characters")
else if Text.Range([ID], 0, 3) <> "000" then Error.Record("First 3 digits not 000") else if Value.Is(
    Value.FromText(
        Text.Range([ID], 3, 5)), 
        type number) 
= false then Error.Record("Last 5 digits not a number")  
else "ok")
in
    #"Added Custom1"

 

Note, you might need to add a few more cases to the if clause, if they did not cover all your "ifs" and "buts". Also, the record discloses the reason for which check did not succeed.

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors