Forum Discussion

Yonah's avatar
Yonah
Helper II
3 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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