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

[jaliu1290]

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

Suppliers	Supplier SKU	Desired Result
ACG,LLB,TTY	x1x_1x1,k1234,gg4455	x1x_1x1
ONO,ACG	__772,j1j_k1k	j1j_k1k
ONO,XTC,ACG	__884,i0f982,l1l_l2l	l1l,l2l

Please help me write this in Power Query!

Thank you in advance to all.

[parry2k]

@jaliu1290 glad to hear.

[parry2k · Accepted solution]

@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]

Brilliant! This worked perfectly, thank you!