Power Query - Text.Contains on a Column of another Table

[ChandeepChhabra]

Hi Everyone, I have a following problem..

Consider 2 tables

Course Master (about 1100 rows) - Only one column as Lecture Code (Contains unique values)

Lecture Codes

NIM1L4A

NIM1L4H

NIM2L4A

NIM2L4F

NIM2L4F_1A

NIM2L4F_1B

NIM2L4F_1C

NIM2L4G

NIM3L1A

NIM3L4A

NIM4L4H

Transaction Table (about 200k rows) - 

1. Start and End Date & Time are pretty straightforward.
2. The Keypress column contains the lecture codes a user accessed from the course. Along with other junk characters

Start Time	End Time	Keypress
29-06-2019 18.45	29-06-2019 18.54	{"data":"Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,NIM1L4H,Outro$Module:2$Sub-module:1$prompt:Intro"}
29-06-2019 18.53	29-06-2019 18.54	{"data":"Module:2$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM2L4A,NIM2L4B-wrong,NIM2L4C-right,NIM2L4D-wrong,NIM2L4E-wrong,NIM2L4F-wrong,NIM2L4G,NIM2L4H,Outro$Module:1$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,Outro$Module:3$Sub-module:1$prompt:NIM3L1A"}
29-06-2019 22.09	29-06-2019 22.09	{"data":"Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM3L4A,NIM3L4B-wrong,NIM3L4C-wrong,NIM3L4D-wrong,NIM3L4E-wrong,NIM3L4F-wrong,NIM3L4G,NIM3L4H,Outro$Module:4$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM4L4A,NIM4L4B-wrong,NIM4L4C-right,NIM4L4D-wrong,NIM4L4E-wrong,NIM4L4F-wrong,NIM4L4G,NIM4L4H,Outro$Module:1$Live_Session:2019-07-02 10:30$"}

The Problem - Since the Keypress column has the lecture code wrapped along with other junk charaters I add 2 columns in the transaction table by extracting the first Lecture Code and last lecture code

The output should look like this

Start Time	End Time	Keypress	First Code	Last Code
29-06-2019 18.45	29-06-2019 18.54	{"data":"Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,NIM1L4H,Outro$Module:2$Sub-module:1$prompt:Intro"}	NIM1L4A	NIM1L4H
29-06-2019 18.53	29-06-2019 18.54	{"data":"Module:2$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM2L4A,NIM2L4B-wrong,NIM2L4C-right,NIM2L4D-wrong,NIM2L4E-wrong,NIM2L4F-wrong,NIM2L4G,NIM2L4H,Outro$Module:1$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,Outro$Module:3$Sub-module:1$prompt:NIM3L1A"}	NIM2L4A	NIM3L1A
29-06-2019 22.09	29-06-2019 22.09	{"data":"Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM3L4A,NIM3L4B-wrong,NIM3L4C-wrong,NIM3L4D-wrong,NIM3L4E-wrong,NIM3L4F-wrong,NIM3L4G,NIM3L4H,Outro$Module:4$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM4L4A,NIM4L4B-wrong,NIM4L4C-right,NIM4L4D-wrong,NIM4L4E-wrong,NIM4L4F-wrong,NIM4L4G,NIM4L4H,Outro$Module:1$Live_Session:2019-07-02 10:30$"}	NIM3L4A	NIM4L4H

You can download the data here. I have tried cartesian join and it does'nt help since the data set is quite large.

Thanks so much

[Zubair_Muhammad · Accepted solution]

@ChandeepChhabra 

Please see attached file with your data. Let me know if it is useful

[ChandeepChhabra]

Thanks @Zubair_Muhammad . Barring a few exceptions, it's almost there.

I'll fine tune it. Many thanks!

[VasTg]

@ChandeepChhabra 

Refer to this post.. Its quite similar except you have to loop thru another table to extract the correct data.

https://community.powerbi.com/t5/Desktop/extract-part-of-a-text-string/m-p/932219

Let us know.

If it helps, mark it as a solution

Kudos are nice too