Forum Discussion

ChandeepChhabra's avatar
ChandeepChhabra
Icon for Impactful Individual rankImpactful Individual
6 years ago
Solved

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

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 TimeEnd TimeKeypress
29-06-2019 18.4529-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.5329-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.0929-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 TimeEnd TimeKeypressFirst CodeLast Code
29-06-2019 18.4529-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"}NIM1L4ANIM1L4H
29-06-2019 18.5329-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"}NIM2L4ANIM3L1A
29-06-2019 22.0929-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$"}NIM3L4ANIM4L4H

 

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

 

Need Help

3 Replies