Column value from row

[deaconb]

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.

I'm pretty new to Power BI and I'm just at a loss for how to even accomplish this. Any help or guidance with this is greatly appreciated!! 

[v-heq-msft]

Hi @deaconb ,
Thanks to @tamerj1 and @DataNinja777  for thier methods.
Here's how I solved it
Create column

Trainer = 
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

[tamerj1]

Hi @deaconb 
Hope this is what you're looking for

Table 2 = 
SUMMARIZE ( 
    'Table',
    'Table'[ResponseID],
    "Trainee", MAXX ( FILTER ( 'Table', 'Table'[Role] = "Trainee" ), 'Table'[Name] ),
    "Trainer", MAXX ( FILTER ( 'Table', 'Table'[Role] = "Trainer" ), 'Table'[Name] )
)

[deaconb]

hey - this result is exactly what I'm looking for. However, when I insert it into my query as a column, it gives me an error of a cyclic reference. 

[tamerj1]

@deaconb 

What is your query?

[deaconb]

The source data is a full dataset of training information. I truncated the data in my example to only the columns I care about/need to work with. 

When I applied your logic as a Custom Column step in Power Query, I received this error: 

Expression.Error: A cyclic reference was encountered during evaluation.

When I applied your logic as a new Column in Power BI table view, I received this error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[tamerj1]

@deaconb 
You can simply group by Response ID and use if statement in the aggregation.

[DataNinja777 · Accepted solution]

Hi @deaconb 

There are many ways to achive your required output, and one example is as shown below:

I attach an example pbix file.  

[deaconb]

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?

[DataNinja777]

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,

[deaconb]

Thanks. I'll just use the dax output.