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

[NikhilPai]

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 

[dufoq3]

Hi @NikhilPai, different approach here:

Result:

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

[NikhilPai]

This works superbly. Many thanks for the help!! 

[NikhilPai]

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 

[tackytechtom]

Hi @NikhilPai ,

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

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

[tackytechtom · Accepted solution]

Hi @NikhilPai ,

How about this? 🙂

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/