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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Yonah
Helper II
Helper II

Transform from Excel to Power Querry

Hi I'm trying to transfer the following form from Excel to Power Querry, unfortunately I can't get the same result.
Probably it is a really small error that is responsible for this.

 

=IF(AND(A2<>0;LEFT(B2="GN "));"";IF(NOT(ISERROR(SEARCH("ST#";B2)));2;IF(NOT(ISERROR(SEARCH("RT#";B2)));1;IF(NOT(ISERROR(SEARCH("PN#";B2)));3;))))

The A column is the article number column and the B column the article description

My function in Power Query looks like this

 

if 
    (Text.Contains(Text.Start([articledescription],3), "GN ")) and [articlenumber]<>0 
    then "" 
    else if Text.Contains([articledescription], "ST#")
    then 2
    else if Text.Contains([articledescription], "RT#")
    then 1
    else if Text.Contains([articledescription], "PN#")
    then 3
    else ""

 

Hopefully who is smarter than me and finds my error

 

Thanks

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Yonah - it appears to be largely correct, but the formula will create a confusing data type becasue 1, 2 & 3 are numbers and "" is text.  You could replace using the following options: (1) switch "" with null, or replace 1, 2, & 3 with "1", "2" or "3" - see below.  

If this does not help, could you please share the error message that you are seeing?  

if 
    (Text.Contains(Text.Start([articledescription],3), "GN ")) and [articlenumber]<>0 
    then "" 
    else if Text.Contains([articledescription], "ST#")
    then "2"
    else if Text.Contains([articledescription], "RT#")
    then "1"
    else if Text.Contains([articledescription], "PN#")
    then "3"
    else ""

 

if 
    (Text.Contains(Text.Start([articledescription],3), "GN ")) and [articlenumber]<>0 
    then null 
    else if Text.Contains([articledescription], "ST#")
    then 2
    else if Text.Contains([articledescription], "RT#")
    then 1
    else if Text.Contains([articledescription], "PN#")
    then 3
    else null

 

View solution in original post

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Yonah - it appears to be largely correct, but the formula will create a confusing data type becasue 1, 2 & 3 are numbers and "" is text.  You could replace using the following options: (1) switch "" with null, or replace 1, 2, & 3 with "1", "2" or "3" - see below.  

If this does not help, could you please share the error message that you are seeing?  

if 
    (Text.Contains(Text.Start([articledescription],3), "GN ")) and [articlenumber]<>0 
    then "" 
    else if Text.Contains([articledescription], "ST#")
    then "2"
    else if Text.Contains([articledescription], "RT#")
    then "1"
    else if Text.Contains([articledescription], "PN#")
    then "3"
    else ""

 

if 
    (Text.Contains(Text.Start([articledescription],3), "GN ")) and [articlenumber]<>0 
    then null 
    else if Text.Contains([articledescription], "ST#")
    then 2
    else if Text.Contains([articledescription], "RT#")
    then 1
    else if Text.Contains([articledescription], "PN#")
    then 3
    else null

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors