Forum Discussion

Apollo71's avatar
Apollo71
New Member
2 years ago
Solved

Group By Query

Hi,   I am trying to create a Power BI form with the following data, but group by serial number and showing the latest service date and corresponding name of the engineer.   Serial number    Serv...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    here is a workaround for you

    try to create a rank column

    = Table.AddColumn(#"Changed Type","Rank",each Table.RowCount(Table.Distinct(Table.SelectRows(#"Changed Type",(x)=>x[SD]>[SD] and x[SN]=[SN]),"SD"))+1)

     

    then filter rank =1

     

     

    at last you can delete rank column

    pls see the attachment below

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Apollo71 ,

ryan_mayu Good answer!
And if you must want to use Group by, you can try this way:
First, Duplicate the Table:

In the new table Table(2), use Group by:

Then in the Table(2), click Merge Queries as New:

Configure as shown in the following figure:

Match the Serial number column of the two tables, and have the Max and Service Date columns match.
The output is as below:

Click here to expand the Table column:

And the final output is as below:


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.