Text help

[Anonymous]

The task is this:

If I have a part number like.....

775-7 (or higher)

786-7 (or higher)

809-8 (or higher)

69-1 (or higher)

...... then check for a certain thing to happen.

Now, I import part numbers as text because of the dashes that are in some of the part numbers.  Here are some of the part numbers that I would be checking:

775-7-6-4  (would qualify because it is a version of 775-7)

775-6-2         (would not qualify because it is lower than 775-7)

786-8-7-2  (would qualify because it is higher than 786-7)

69-1-0    (would qualify because it is a version of 69-1)

As you can see, the only thing I really need to confirm in this situation is if the first number after the dash is equal to or greater than a certain number.  But, as I said prior, I import part numbers in as text.

I think I want something like this:

Return the first character after the dash, convert it to a number, is it greater than or equal to 7 if the part number starts with 775, if the part number starts with 809, is it greater than or equal to 8........

..

Since I'm only dealing with 10 total scenarios, I don't mind just building a 10 option if statement.

Please let me know your suggestions on how best to deal with this.

[MarcelBeug]

Just split the part numbersm keep the first2 columns and do your check.

You need 2 tables, 1 with the valid part numbers and 1 with the part numbers you want to check.

ValidPartNo (in Excel with connection only):

let
    Source = Excel.CurrentWorkbook(){[Name="dPartNo"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part-No", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Part-No",Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),{"Part-No.1", "Part-No.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Part-No.1", Int64.Type}, {"Part-No.2", Int64.Type}})
in
    #"Changed Type1"

CheckPartNo:

let
    Source = Excel.CurrentWorkbook(){[Name="fPartNo"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part-No", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Part-No", "Part-No - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","Part-No - Copy",Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),{"Part-No - Copy.1", "Part-No - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Part-No - Copy.1", Int64.Type}, {"Part-No - Copy.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Part-No - Copy.1", "Part-No - Part1"}, {"Part-No - Copy.2", "Part-No - Part2"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Part-No - Part1"},VallidPartNo,{"Part-No.1"},"Valid",JoinKind.LeftOuter),
    #"Expanded Valid" = Table.ExpandTableColumn(#"Merged Queries", "Valid", {"Part-No.1", "Part-No.2"}, {"Valid.Part-No.1", "Valid.Part-No.2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Valid", "Valid?", each if [#"Valid.Part-No.1"] is null then false else [#"Part-No - Part2"] >= [#"Valid.Part-No.2"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Part-No - Part1", "Part-No - Part2", "Valid.Part-No.1", "Valid.Part-No.2"})
in
    #"Removed Columns"

[Anonymous]

Thanks for your time and reply.  However, I am pulling this information into Power BI desktop from our sql database.  I am never touching excel.

I believe I need a dax formula to solve my issue.

[MarcelBeug]

This code can also be used with SQL as data source (however I'm not sure if this code allows for complete query folding).

Otherwise if DAX is the prefered solution, it's fine with me, but DAX is not my specialism.

[CahabaData · Accepted solution]

A > = < compare needs to be with numbers; so I would use the query editor to create working tables and convert these i.e.

775-7 (or higher)

786-7 (or higher)

809-8 (or higher)

69-1 (or higher)

transform to:

c1     c2

775   7757

786   7867

809   8098

69     691

part numbers:

775-7-6-4  (would qualify because it is a version of 775-7)

775-6-2         (would not qualify because it is lower than 775-7)

786-8-7-2  (would qualify because it is higher than 786-7)

69-1-0  

transform to:

c1   c2

775   7757

775   7756        

786   7868

69     691

column 1 is the core part number to join on - then compare c2 of each table;  then the DAX statement is much more straight forward.....