Forum Discussion
Column value from row
We have training data that looks like below:
| Name | Role | ResponseID | Trainer |
| A | Trainee | 7125417 | Z |
| Z | Trainer | 7125417 | |
| A | Trainee | 7125461 | Y |
| Y | Trainer | 7125461 | |
| B | Trainee | 7125955 | X |
| X | Trainer | 7125955 | |
| B | Trainee | 7125978 | X |
| X | Trainer | 7125978 |
A Trainee can complete several different training events either with a different Trainer each time or the same trainer over multiple times. Each time a Trainee compelete a training, the training event is stored as a transaction in the database by "ResponseID"; these increment up 1 for each training event, so it can be used to order things chronologically 1-N, where item 1 was completed before item 2, etc. The trainer and trainee are logged to the same "ResponseID" and differentiated by the "Role" column.
I'd like to have the data as depicted above in the "Trainer" column. So essentially, I want to have a single row of each "ResponseID" by making the "Trainer" a column instead of keeping it as "second" row.
Hi deaconb
There are many ways to achive your required output, and one example is as shown below:
I attach an example pbix file.
10 Replies
Hi deaconb ,
Thanks to tamerj1 and DataNinja777 for thier methods.
Here's how I solved it
Create columnTrainer = VAR _result = CALCULATE( MAX('Table'[Name]), FILTER( ALLEXCEPT( 'Table', 'Table'[ResponseID] ), 'Table'[Role] = "Trainer" ) ) RETURN IF( 'Table'[Role] = "Trainee", _result, BLANK() )Create a calculated table
Result = SELECTCOLUMNS( FILTER('Table','Table'[Trainer] <> BLANK()), "ResponseID",'Table'[ResponseID], "Trainee",'Table'[Name], "Trainer",'Table'[Trainer] )Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi deaconb
There are many ways to achive your required output, and one example is as shown below:
I attach an example pbix file.
Hi DataNinja777 this works as a new Column in Power BI table view, but it isn't working as a Custom Column step in Power Query (I understand they are different).
I'd prefer to do this in Power Query so I can then delete the "Trainer" row all togther, after the new column is created, which makes the remaining dataset 50% less rows as the only difference between the raw dataset rows is the Trainer row and Trainee row.
Do you know what modifications I'd need to make to get this to work through Power Query?
Hi deaconb
You have your required output in dax formula from multiple people, but if you want to use Power Query instead, you can create a table like below using Power Query Ribbon clicking technique.
Best regards,
