cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Flona Frequent Visitor
Frequent 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

Accepted Solutions
Super User
Super User

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

@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

Highlighted
Super User
Super User

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

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

Super User
Super User

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

@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
Super User
Super User

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

@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

Highlighted
Super User
Super User

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

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

Flona Frequent Visitor
Frequent Visitor

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

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

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

@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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 111 members 1,574 guests
Please welcome our newest community members: