Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) -
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
Solved! Go to Solution.
@ChandeepChhabra
Please see attached file with your data. Let me know if it is useful
@ChandeepChhabra
Please see attached file with your data. Let me know if it is useful
Thanks @Zubair_Muhammad . Barring a few exceptions, it's almost there.
I'll fine tune it. Many thanks!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |