Add column in power query that calculates last date for each id

[Anonymous]

Hi, I am trying to replicate the below dax in power query, so that i can add a column in power query and remove duplicates.

For each customer want to calculate latest date and do it in power query.

Customer Id	Date	Desired Result
1	1/1/2020	3/1/2020
1	3/1/2020	3/1/2020
2	2/1/2020	6/1/2020
2	4/1/2020	6/1/2020
2	6/1/2020	6/1/2020
3	4/1/2020	6/1/2020
3	6/1/2020	6/1/2020

Current dax = CALCULATE(LASTDATE('Table'[Date]),ALLEXCEPT('Table','Table'[Customer Id]))

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjA6VYHaiQMYqQEUjICFPIBFPIDEXIGFOVMZqqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Id"}, {{"Rows", each _, type table [Customer Id=number, Date=date]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "MaxDate", each List.Max([Rows][Date])),
#"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Customer Id", "Date"}, {"Customer Id", "Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"MaxDate", type date}})
in
#"Changed Type1"

[AlB · Accepted solution]

@Anonymous 

Your requirement was confusing, as you said "...so that i can add a column in power query and remove duplicates"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjA6VYHaiQMYqQEUjICFPIBFPIDEXIGFOVMZqqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LatestDate", each List.Max(Table.SelectRows(#"Changed Type",(inner)=>inner[Customer Id] = [Customer Id])[Date]))
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[AlB]

Hi @Anonymous 

Just use Group By:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjA6VYHaiQMYqQEUjICFPIBFPIDEXIGFOVMZqqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Id"}, {{"LatestDate", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Anonymous]

Hi @AlB thanks for your reply.

Would this logic reduce the number of rows i assume?

if so, this wouldnt really help because I need to keep all rows because then i need to apply other filterss to remove duplicates. so I need to create a brand new column without removing rows.

If it is not removing rows, how do i apply the code you wrote?

[AlB · Accepted solution]

@Anonymous 

Your requirement was confusing, as you said "...so that i can add a column in power query and remove duplicates"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjA6VYHaiQMYqQEUjICFPIBFPIDEXIGFOVMZqqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LatestDate", each List.Max(Table.SelectRows(#"Changed Type",(inner)=>inner[Customer Id] = [Customer Id])[Date]))
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Anonymous]

Sorry to be pedantic but i want to understand the logic behind the code more than solving my problem.

what if I want to have instead of the max of date, the max of an integer?

First time any customer id appear in a row has a 1, second time a 2 and so on. i would want the max of this numeric column to appear in each row for each customer id.

[dax · Accepted solution]

Hi @Anonymous , 

You could click each steps to understand it in Edit Queries

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyUIrVgQgYIwsYAQWM0AVM0AXMkAWM0VUYo6iIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Id"}, {{"max", each List.Max([Date]), type date}, {"all", each _, type table [Customer Id=number, Date=date]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date"}, {"Date"})
in
    #"Expanded all"

If you want to get max value, you also could change field(date) in this windows

This is group by customer id, then create two columns which contains max date of group and which contain table type value(all columns in original table). Then you could choose column you want when expand table

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi, thanks for your reply again,

I copied the code when creating a custom column and i get the custom column saying "table", i then select only the "Latest Date" and the column appears. however the date in the new column is not the latest column of the specific customer id (not even the first one or any date for that customer id).

(Pl.Gl. is Date)

Is it how i should do it or am i doing something wrong? 

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjA6VYHaiQMYqQEUjICFPIBFPIDEXIGFOVMZqqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Id"}, {{"Rows", each _, type table [Customer Id=number, Date=date]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "MaxDate", each List.Max([Rows][Date])),
#"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Customer Id", "Date"}, {"Customer Id", "Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"MaxDate", type date}})
in
#"Changed Type1"

[BarisT]

The problem with this code is it shows only the last date of the customerID multiple times, when the same customer is created multiple times. It should everytime take dynamically the latest date of this customer. Unfortunately power bi is not able to solve this, neither the community.