Split columns with delimited based on records from another table

[bpatterson]

Hello,

I got two tables one with the values that has delimited characters acsending numbers and the second table with the colunms names and their delimited characters, how can I match the columns names and pull only their data for each column based on the delimited and their id. Some values could have 1 to 6 different columns depending on the data. I have attached some examples below to explain it better. 

data table

id	values
103	1A12ABOVE3333
105	41055Above6Subject
106	116

reference table

id	tsc-ref	tsc-order	sdesc
105	4	3	Score
105	5	2	Level
105	6	1	Subject
103	1	1	subject
103	2	2	level
103	3	3	scale score
106	1	1	Score

Results

id	subject	level	score
103	A1	Above	333
105	Subject	Above	105
106			16

I would like to pull any word score and get their tsc-ref to get the value data only the score position or any other fields. Does anyone has any suggestions? 

Thank you

Brian

[some_bih]

Hi @bpatterson create relatioship between tables like on link below and after that create visual as you need (table / matrix).

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships 

[bpatterson]

Hello, 

Thank you for the information, I do have all the relationships setup and the matrix table would work but it does not break up the values into columns. How would I be able to split up the values into columns? For example: column/field value 41055Above6Subject. 

Column 4	Column 5	Column 6
105	Above	Subject

Thank you again

Brian

[some_bih · Accepted solution]

Hi @bpatterson one of posdible solution is using column by example

https://learn.microsoft.com/en-us/power-query/column-from-example