Forum Discussion

jaliu1290's avatar
jaliu1290
Frequent Visitor
1 year ago
Solved

Power Query - Using index of string by delimiter to retrieve string from another column

Hello Power BI Community!

 

I have an Excel sheet of products with various suppliers and their respective supplier SKU codes which are separated by a "," delimiter.

 

Each supplier SKU is associated with each supplier via the index position of the strings separated by delimiters.

 

For example in row two, the string "ACG" is index #2 in "Suppliers", so to return its supplier code it should be index #2 in "Supplier SKU" which is j1j_k1k.

 

I need to retrieve all SKU codes related to supplier "ACG".

 

SuppliersSupplier SKUDesired Result
ACG,LLB,TTYx1x_1x1,k1234,gg4455x1x_1x1
ONO,ACG__772,j1j_k1kj1j_k1k
ONO,XTC,ACG__884,i0f982,l1l_l2ll1l,l2l

 

Please help me write this in Power Query!

 

Thank you in advance to all.

Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    jaliu1290 you can simply achieve this by adding a new custom column in Power Query, make sure the column names are as per table and it is case sensitive.

     

    Text.Split([Supplier SKU], ","){List.PositionOf(Text.Split([Suppliers],","), "ACG")}

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    jaliu1290 you can simply achieve this by adding a new custom column in Power Query, make sure the column names are as per table and it is case sensitive.

     

    Text.Split([Supplier SKU], ","){List.PositionOf(Text.Split([Suppliers],","), "ACG")}

     

    • jaliu1290's avatar
      jaliu1290
      Frequent Visitor
      1 year ago

      Brilliant! This worked perfectly, thank you!