Convert rows to columns pivot while joining two tables - Power BI

[Anonymous]

Hi,

I have a TableA where i have the Job Detail and another TableB holds the statuses of a job. in Power BI, i need to show the Job Details and each statuses as columns with the value as Max(StatusDateTime) on the same row as job details. Can somebody tell me the best way to acheive this result please? e.g. scenario:

TableA    

JobNo	JobName	CreatedDate
S1234	Test1	01/01/2020

Table B

JobNo	StatusDateTime	Status
S1234	02/02/2020 03:35:23	DR
S1234	02/02/2020 03:55:23	DR
S1234	02/02/2020 04:38:23	DR
S1234	02/02/2020 15:35:23	VC
S1234	02/02/2020 15:40:23	VC
S1234	02/02/2020 16:33:23	PC
S1234	02/02/2020 16:35:23	PC

ExpectedResultTable

JobNo	JobName	CreatedDate	DR	VC	PC
S1234	Test1	01/01/2020	02/02/2020 04:38:23	02/02/2020 15:40:23	02/02/2020 16:35:23

Thanks in Advance.

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution, that uses current structure and matrix visual.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

This is my scenario. So i retreive TableA and TableB from the database and then create  a new table using dax called JobStatus.

JobStatus = SELECTCOLUMNS('@TableB',"DocEntry",[DocEntry],"UpdateDate",[UpdateDate],"Status",[U_Status],"Age", DATEDIFF([UpdateDate],TODAY(),DAY)).

Now if i want to Pivot JobStatus table i can't do it because it does not appear in Power Query Editor. Only two tables appears there which i imported from the database. JobStatus table does not appear.

I hope, I have managed to explain well.

Thanks.

[Mariusz]

Hi @Anonymous 

You can pivot your Table B with the setup as below.

Later just Merge the tables on JobNo

Also, see the attached for the ref.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi, Thanks for your reply. Actualy Table2 is made using DAX so i cannot use Pivot functionality of Power query editor. Any other way of doing it?