cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChandeepChhabra Solution Specialist
Solution Specialist

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

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

@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

View solution in original post

3 REPLIES 3
Super User I
Super User I

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

@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

View solution in original post

Super User III
Super User III

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

@ChandeepChhabra 

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

 

Try my new Power BI game Cross the River
ChandeepChhabra Solution Specialist
Solution Specialist

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

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

I'll fine tune it. Many thanks!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors