Create Table based on another table according to criteria

[jt024]

hi there,

I'm new to PBI and I'm hoping someone here can help me please.

I have a table coming into PBI with the following structure:

Item	Date1	Date2	Date3
1	1/1/2020	2/5/2020	3/6/2020
1	2/2/2020	5/5/2020
2	2/24/2020	5/31/2020	6/6/2020
3	1/20/2020	3/6/2020
3	2/14/2020	3/12/2020	5/5/2020
3	6/3/2020
4	5/7/2020
4
5

I would like to create a table based on the above that looks like this:

Item	Date1	Date2	Date3
1	2/2/2020	5/5/2020
2	2/24/2020	5/31/2020	6/6/2020
3	6/3/2020
4
5

So basically in the second table:

1. Item has to be unique

2. The rules for selecting the dates for Date1, Date2 and Date3 columns are:

  - If the item has multiple dates, select the latest date and place that on the table. (for example, in Item 1 in the first table, Item 1 appears twice and there are dates for each in Date1 column, then select the latest date. Same as Date2, there are 2 dates so select the latest date. But in Date3 column, only one has a date so ignore this / show no value).

  - If the item has no date , then no value is shown

  - If the item appears more than once and one of them has no date, then no value is shown (for example, in Item 4 in the first table, Item 4 appears twice but in the date1 column there is only 1 date filled out instead of two, then no date should be shown).

How do I get Table 2 in PowerBI? Should I do this in PowerQuery (M formula?) or Dax?

Can someone please point me in the right direction? 

Thank you in advance for your help. It is greatly appreciated.

~N~

[jt024]

Thank you everyone for your help. I was thinking of something complicated like creating each column using a formula and all that and counting duplicates and then comparing them, but I'm glad the Grouping in PowerQuery made it easier. Thank you for teaching this feature to me. Appreciate your help!

[Christophe]

@jt024 Here is a trick to work around your special rule with empty cells. btw note that in Power Query the cells will be marked as null rather than empty.

1. In Power Query, select the three date columns, and replace all null cells with a date far in the future, for example 9/9/9000.

2. Select the item column, Transform > Group By as follows:

 3. Use replace to switch 9/9/9000 back to null.

Here is the final result:

[mahoneypat · Accepted solution]

This is easier to do in query.  Below is some example M on how to do it with your data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The modified #"Grouped Rows" step is the one you will need to adapt to your query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY1BDsAgCAS/YjybIAvaxxj//402GEqNvW3YYXaMzLlkJiZU1CeCmkehvuIsCwPBuxZYsh6r1wDkdfavSGwP9Vhxk5iJNQA+Zx3sJPHtBjXy+i/Sdmn7Zd4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date1 = _t, Date2 = _t, Date3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Date1", each if List.Contains([Date1], null) then null else List.Max([Date1]), type nullable date}, {"Date2", each if List.Contains([Date2], null) then null else List.Max([Date2]), type nullable date}, {"Date3", each if List.Contains([Date3], null) then null else List.Max([Date3]), type nullable date}})
in
    #"Grouped Rows"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Power Query would be an easier approach with following transformations
1. Use Unpivot Columns -> Select Column - Item and under Unpivot Columns select unpivot other columns

2. Use Group By --> In group by window, select Item as the column, Operation - Max and value under column 

you should have 1 entry for an item with Max date