Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @bpatterson one of posdible solution is using column by example
https://learn.microsoft.com/en-us/power-query/column-from-example
Proud to be a Super User!
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
Proud to be a Super User!
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
Hi @bpatterson one of posdible solution is using column by example
https://learn.microsoft.com/en-us/power-query/column-from-example
Proud to be a Super User!
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |