Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Anonymous
Not applicable

Text help

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

Capture.PNG..

 

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

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

www.CahabaData.com

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
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.

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)

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

www.CahabaData.com

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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