Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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 JobNameCreatedDate
S1234Test101/01/2020

 

Table B

JobNoStatusDateTimeStatus
S123402/02/2020 03:35:23  DR
S123402/02/2020 03:55:23  DR
S123402/02/2020 04:38:23  DR
S123402/02/2020 15:35:23  VC
S123402/02/2020 15:40:23  VC
S123402/02/2020 16:33:23  PC
S123402/02/2020 16:35:23  PC

 

ExpectedResultTable

JobNo JobNameCreatedDateDRVCPC
S1234Test101/01/202002/02/2020 04:38:23 02/02/2020 15:40:23  02/02/2020 16:35:23  

 

Thanks in Advance.

Need Help

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.