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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
Flona
Regular Visitor

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

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

3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@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"

 

View solution in original post

AlB
Community Champion
Community Champion

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] ) )
)

 

View solution in original post

AlB
Community Champion
Community Champion

@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] ) )
)

View solution in original post

4 REPLIES 4
AlB
Community Champion
Community Champion

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
Regular Visitor

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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"

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors