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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jmdaily83
Helper I
Helper I

How to assign chronological order to dates by created date for Project in Power Query - No expansion

Hello,

 I have a report in which the client created it in Excel and I'm running into trouble trying to replicate a lot of the stuff they did in PowerBI since you cannot make selection by cells. As a work around for one of the items I'm attempting to assign a chronological order based on the created date (newest to oldest) for items that have been submitted for different projects in the same column.  I have tried this with some help from other users in the community but my issue is that it is expanding the rows and making my 500 row table over 80,000 rows, which is not what I want. 

 

Below is a sample of the data that I have 

ProjectDate Created
A4/25/2023
A4/19/2023
B11/20/2023
B11/15/2023
B6/1/2023
B5/31/2023
B5/1/2023
B4/30/2023
C11/27/2023
C5/5/2023
C2/7/2023
C1/12/2023
C12/16/2022
C12/16/2022
C12/8/2022
D8/25/2023
D5/19/2023
E9/22/2023
E9/19/2023
E9/13/2023
F6/26/2023
F6/23/2023
G5/15/2023

 

 

and the Order column below is what I would like to add to show the results as below:

ProjectDate CreatedOrder
A4/25/20231
A4/19/20232
B11/20/20231
B11/15/20232
B6/1/20233
B5/31/20234
B5/1/20235
B4/30/20236
C11/27/20231
C5/5/20232
C2/7/20233
C1/12/20234
C12/16/20225
C12/16/20226
C12/8/20227
D8/25/20231
D5/19/20232
E9/22/20231
E9/19/20232
E9/13/20233
F6/26/20231
F6/23/20232
G5/15/20231
1 ACCEPTED SOLUTION

@jmdaily83 OK, how about this to add a custom column?

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
__Project = [Project],
__Date = [Date Created],
__Table = Table.SelectRows( #"Changed Type", each ([Project] = __Project and [Date Created] > __Date ) )
in
  Table.RowCount(__Table) + 1)

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

In Power Query, it is fairly efficient to GroupBy Project, then add an Index Column within the aggregation.

It seems your original data is appropriately sorted. If that is not the case, then you will need to add a sorting step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBLDoAwCATQu3Rtgkyl6tL/IYz3v4b4S0u7cMkLYcjsuxtc5RqCEGp4d1SfcB9lVGHWuSAWS4HYgpAvJIOGfHJ4erNaS0JiAZRt6DPIBMThIvxQF2VW6Uwd8/NzUseioiNyKXfYR1nvehAKSXa2J+tLP04=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Date Created" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Date Created", type date}}),

//if data not sorted, will need to add a sorting step by Project and Date Created

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {
        {"all", each Table.AddIndexColumn(_,"Order",1,1,Int64.Type),

            type table [Project=nullable text, Date Created=nullable date, Order=Int64.Type]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date Created", "Order"}, {"Date Created", "Order"})
in
    #"Expanded all"

Results

ronrsnfld_0-1718308927395.png

 

 

 

Thank you, this method worked as well!

Greg_Deckler
Super User
Super User

@jmdaily83 Not sure how to do that exactly in Power Query but perhaps you can use a DAX solution or adapt the DAX solution to M code:

Cthulhu - Microsoft Fabric Community


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@jmdaily83 OK, how about this to add a custom column?

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
__Project = [Project],
__Date = [Date Created],
__Table = Table.SelectRows( #"Changed Type", each ([Project] = __Project and [Date Created] > __Date ) )
in
  Table.RowCount(__Table) + 1)

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

This worked perfectly, thank you so much!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors