cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

Text help

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.

1 ACCEPTED SOLUTION
Memorable Member

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.....

4 REPLIES 4
Community Champion

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"```
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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.

Community Champion

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.

Specializing in Power Query Formula Language (M)
Memorable Member

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.....

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors