Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous - 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
Employee
Employee

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
v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors