cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AkosKanto
Regular Visitor

Creating a Latest Status column in database

Hello PBI Experts,

 

I have a question for you ! 🙂 I have multiple lines for the same investment (or projects) depending on its progress in a workflow ( Pre-Approval / Approval / N/A) and I have same kinds of message (approved / pending) per workflow progress.

 

I would like to add an extra column ("Latest Status" ) in Power Query inside PBI which displays for each line the latest status per investment in the following way:

 

PBI.png

Your help is much appreciated.

 

3 REPLIES 3
KNP
Super User
Super User

@AkosKanto - do you have a date or timestamp column in the data? You kind of need a column to order by if you want the "latest".

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Ehren
Microsoft
Microsoft

One approach to this:

  • Group the rows by Investment, creating an aggregate column containing "All Rows"
  • Sort the rows contained in each group so that the latest status is ordered last
    • If you can't use an alphanumeric sort, try a custom comparer, or simply map each value to an integer column that can be sorted on
  • Add a custom column to the rows in each group that contains the status from the last row in the group
  • Expand
Eyelyn9
Community Support
Community Support

Hi @AkosKanto ,

 

According to the screenshot, I'm not clear about the logic of the “Latest Status”.

 

For ABC1, the latest row is Approval & Approved , it's right. But for ACB2, the latest row is Approval & N/A , why the final result  is Approval  & Pre-Approval ?

Eyelyn9_0-1632361691228.png

 

The rule I can find is that there may be an order for status ,like 

Workflow: From N/A --> Pre-Approval --> Approval 

Message: From  N/A --> Approval Pending --> Approved

 

Let's take an example:

For ABC1 , firstly Message has two values: Approval Pending and Approved. So the latest message is Approved, then according to this, find two Workflows as well: Pre-Approval  and Approval. so the lastest workflow is Approval

 

Could you please explain more to help us clarify your scenario?

 

Best Regards,
Eyelyn Qin

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors