cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create Table based on another table according to criteria

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:

ItemDate1Date2Date3
11/1/20202/5/20203/6/2020
12/2/20205/5/2020 
22/24/20205/31/20206/6/2020
31/20/20203/6/2020 
32/14/20203/12/20205/5/2020
36/3/2020  
45/7/2020  
4   
5   

 

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

ItemDate1Date2Date3
12/2/20205/5/2020 
22/24/20205/31/20206/6/2020
36/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~

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Create Table based on another table according to criteria

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Helper III
Helper III

Re: Create Table based on another table according to criteria

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

Highlighted
Super User VI
Super User VI

Re: Create Table based on another table according to criteria

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Advocate I
Advocate I

Re: Create Table based on another table according to criteria

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

 
 

MaxDates.png

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

 

Here is the final result:

MaxDatesFinal.png

 

 

Highlighted
Frequent Visitor

Re: Create Table based on another table according to criteria

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!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors