Return the value based on earliest date creation and by group

[Rondel]

Hello PBI Community,

I have a table with 3 columns (Group, Value Entered, Created On).  I want to create a 4th column (Original date) based on the earliest date creation by group.

Group	Value Entered	Created On	Original Date (*Desired Column)
x	1/1/2024	4/11/2024	1/1/2024
x	3/4/2024	4/12/2024	1/1/2024
x	2/2/2024	5/13/2024	1/1/2024
y	3/2/2024	1/1/2024	3/2/2024
y	1/24/2024	1/2/2024	3/2/2024

[lbendlin · Accepted solution]

ah, got it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTLUN9Q3MjAyATJN9A2h7FgdiKSxvgmSpBGqJJALkzTVNzRGSFaCdcIl4TbA5IBcE4QkzNRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, #"Value Entered" = _t, #"Created On" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value Entered", type date}, {"Created On", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Rows", each _, type table [Group=nullable text, Value Entered=nullable date, Created On=nullable date]}, {"Min Created", each List.Min([Created On]), type nullable date}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Value Entered", "Created On"}, {"Value Entered", "Created On"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Original Date", (k)=> Table.SelectRows(#"Expanded Rows", each [Group]=k[Group] and [Created On]=k[Min Created]){0}[Value Entered], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Group", "Value Entered", "Created On", "Original Date"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @lbendlin  , please allow me to provide another insight: 

Hi  @Rondel ,

Here are the steps you can follow：

1. Create calculated column.

Original Date =
var _mindate=
MINX(
    FILTER(ALL('Table'),
    'Table'[Group]=EARLIER('Table'[Group])),[Created On])
return
MINX(
    FILTER(ALL('Table'),
    'Table'[Created On]=_mindate),'Table'[Value Entered])

2. Result:

Best Regards,

Liu Yang

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

[Rondel]

@v-yangliu-msft @lbendlin - I want to sincerely thank you both for your help and support!  

I learned a ton from both of your recommendations and was able to implement it to our business problem!

[lbendlin]

Your sample data is inconsistent with your request.  Which column is "earliest date creation"  based on?  Why not 1/24/2024 for group y ?

[Rondel]

Hi @lbendlin 

the column [Created On] would be the system generated date.

1/24/2024 would not be for group y since it was entered on 1/2/2024 (oldest date for group y is  1/1/2024).

Hope this helps.

[lbendlin]

Please explain how you arrive at 3/2/2024 for group y.

[Rondel]

Sure thing -

3/2/2024 is the date value entered by an operator.  This same value was created (system generated) on 1/1/2024. 

3/2/2024 is the [Original Date] because the following day (1/2/2024), they changed the date from 3/2/2024 to 1/24/2024.

[lbendlin · Accepted solution]

ah, got it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTLUN9Q3MjAyATJN9A2h7FgdiKSxvgmSpBGqJJALkzTVNzRGSFaCdcIl4TbA5IBcE4QkzNRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, #"Value Entered" = _t, #"Created On" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value Entered", type date}, {"Created On", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Rows", each _, type table [Group=nullable text, Value Entered=nullable date, Created On=nullable date]}, {"Min Created", each List.Min([Created On]), type nullable date}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Value Entered", "Created On"}, {"Value Entered", "Created On"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Original Date", (k)=> Table.SelectRows(#"Expanded Rows", each [Group]=k[Group] and [Created On]=k[Min Created]){0}[Value Entered], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Group", "Value Entered", "Created On", "Original Date"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Rondel]

@lbendlin 

Would you mind explaining what this part of the code does?  I can't seem to make it work on my end: 

Table.AddColumn(#"Expanded Rows", "Original Date", (k)=> Table.SelectRows(#"Expanded Rows", each [Group]=k[Group] and [Created On]=k[Min Created]){0}[Value Entered], type date)

[lbendlin]

You wanted the first listed date in the group. The code filters the previous result to return all rows in the same group and then grabs the column value from the first row.

[Rondel]

Thank you for the quick replies! - ill execute this once I get back to my desk.

Will keep you posted!