Extract distinct data from one table (Source) into an new table

[Flona]

Hi

I have some problem extracting distinct data from on table into another. I have one large source table with 20k+ rows, but only around 3000 distinct IDs (se example under). I would like to extract the row with last start date under each ID. Due to historical data the description may also change on the different rows, and it is always the description on the row with the last start date that are interesting.

I’ve tried remove duplicates (ID Colum) in the source table in Power Query, but are not able to ensure that this does not remove the row with the last start date.

Here is an example of the table data I have in the source file, and how I would like to have it in a new table.

Table 1 (Source)
ID	Description	Start date	End Date
ID1	ID1 Text 1	01.01.2019	01.02.2019
ID1	ID1 Text 2	01.01.2019	01.02.2019
ID1	ID1 Text 3	20.01.2019	01.03.2019
ID2	ID2 Text 1	10.01.2019	20.01.2019
ID2	ID2 Text 1	15.01.2019	25.01.2019
ID3	ID3 Text 1	02.01.2019	03.01.2019
ID3	ID3 Text 1	05.01.2019	06.01.2019
ID3	ID3 Text 1	07.01.2019	08.01.2019
ID3	ID3 Text 2	09.01.2019	10.01.2019
ID4	ID4 Text1	26.01.2019	01.03.2019

Table 2 (New)
ID1	ID1 Text 3	20.01.2019	01.03.2019
ID2	ID2 Text 1	15.01.2019	25.01.2019
ID3	ID3 Text 2	09.01.2019	10.01.2019
ID4	ID4 Text1	26.01.2019	01.03.2019

Anyone have a solution for my problem, or knows how to solve this?

Regards 

Flona

[Zubair_Muhammad · Accepted solution]

@Flona 

You can use Table.Max function in Power Query as follows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdJBC4MgFAfwryKdPeQz27qPYINOBTtEB7eEdmngEta3X3nIqekuuwiPpz/fX2zb5HwiCV5X1Ij3hNaiUeLV8xmjCx8VlzMiGEFKiqVVyofulOImrVaHPQr+R9GlqNVoSZB6VMXlfXAc0A58pRuUdGYyUvCSEOblY95Um8Usi2qLGusq+tHRYMP8qWlcq/mkpHWABVPmcap62tsPZio3/jEswX7EIhKR2K+faS7T3H5EyH/9ie4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t, #"Start date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Description", type text}, {"Start date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All", each Table.Max(_,"Start date"), type record}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"ID", "Description", "Start date", "End Date"}, {"ID.1", "Description", "Start date", "End Date"})
in
    #"Expanded All"

[AlB · Accepted solution]

Hi @Flona 

If you want it in DAX you can create a new calculated table as follows:

NewTable =
FILTER (
    Table1,
    Table1[ID]
        = CALCULATE ( MAX ( Table1[Start date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
)

[AlB · Accepted solution]

Hi @Flona 

If you want it in DAX you can create a new calculated table as follows:

NewTable =
FILTER (
    Table1,
    Table1[ID]
        = CALCULATE ( MAX ( Table1[Start date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
)

[Flona]

Hi

Thank you for your input, I have tried this solution before and cannot get it to work due to different formats, date and text. Error message fon DAX expresion "DAX comparison operatisn do not support comapring values of type Text with values of type Date. Consider using the Value or Format function to convert one of the values

NewTable =

FILTER (Table1;

Table1[ID]

= CALCULATE(MAX(Table1[Start date]);ALLEXCEPT(Table1;Table1[ID]))

)

[AlB · Accepted solution]

@Flona 

You're quite right. There was an error in the code. Try this slightly modified version:

NewTable = 
FILTER (
    Table1;
    Table1[Start date]
        = CALCULATE ( MAX ( Table1[Start date] ); ALLEXCEPT ( Table1; Table1[ID] ) )
)

[Zubair_Muhammad · Accepted solution]

@Flona 

You can use Table.Max function in Power Query as follows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdJBC4MgFAfwryKdPeQz27qPYINOBTtEB7eEdmngEta3X3nIqekuuwiPpz/fX2zb5HwiCV5X1Ij3hNaiUeLV8xmjCx8VlzMiGEFKiqVVyofulOImrVaHPQr+R9GlqNVoSZB6VMXlfXAc0A58pRuUdGYyUvCSEOblY95Um8Usi2qLGusq+tHRYMP8qWlcq/mkpHWABVPmcap62tsPZio3/jEswX7EIhKR2K+faS7T3H5EyH/9ie4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t, #"Start date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Description", type text}, {"Start date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All", each Table.Max(_,"Start date"), type record}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"ID", "Description", "Start date", "End Date"}, {"ID.1", "Description", "Start date", "End Date"})
in
    #"Expanded All"