• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ranking to bring records from last day of the month only

jutu

Member
Hi. If I have the below and want to rank it as below (it's not dense but not sure how you call this sort of ranking index sorry) so that it brings only the last dates calls of the month per country, which would be the best way in your own experience, ranking or grouping? I know there are several ways of achieving this like ranking it as below which I am not sure how to achieve it in Power Query. I am doing it in Power Query but it would be interesting to know how can I achieve the indexing it like in the Rank field below but using DAX instead. I have tried using variables but I don't get it right. Much appreciated
OrigineCallsDateRank
Spain
3​
05-May​
1​
Portugal
8​
01-May​
2​
USA
3​
22-Mar​
1​
Portugal
2​
02-Mar​
2​
USA
8​
01-Mar​
3​
England
2​
24-Feb​
1​
Spain
5​
08-Feb​
2​
England
8​
05-Feb​
3​
USA
6​
05-Jan​
1​
England
3​
01-Jan​
2​
 
First extract month/monthname from date.
Assuming it's actual Date type.
=Date.Month([Date])

Select Custom, Group By, All Rows (No aggregation).

Add custom column (Assuming original table is unsorted).
=Table.AddIndexColumn(Table.Sort([Temp],{"Date", Order.Descending}), "Rank", 1)

Remove all but the above custom column.

Expand {"Origine", "Calls", "Date", "Rank"}.

Change type.

Ex:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.Month([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Temp", each _, type table [Origine=nullable text, Calls=nullable text, Date=nullable date, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn(Table.Sort([Temp],{"Date", Order.Descending}), "Rank", 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Temp"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.1", {"Origine", "Calls", "Date", "Rank"}, {"Origine", "Calls", "Date", "Rank"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Origine", type text}, {"Calls", Int64.Type}, {"Date", type date}, {"Rank", Int64.Type}})
in
    #"Changed Type"
 
Hi. I have attached the dataset which is very very small compared to the large datasets that I work with.
It works like glove so grateful. I think it's an excellent way to go around it.

But when I go to the Grouped Rows step it takes like really very long time to bring that step up even that it's such a small data set.
Would you know why is this? This is a very very small data set and hope it will respond well when working on very large datasets.

How would you go about creating a DAX measure just as an alternative option? I have tried different ways using variables with other datasets and it's always a challenge and if it was this dataset how would you go about it. Many thanks
 

Attachments

  • Book1.xlsx
    14.3 KB · Views: 1
Last edited:
Try using Table.Buffer() to speed things up. Though mileage ma vary by machine spec.

If wanting to do it in DAX either as calculated column or using measure. Have a read of link.
 
Hi, what is your rank logic ?! Why not just sorting dates by country ?​
Or even just sorting only dates and using a cell formula as rank : instant result …​
Can you develop a bit more on this marc please? Essentially what I actually need is to bring only the last read to be precise. The dataset is not just 2 columns but millions or rows with quite a few columns too so grouping it wouldn't quite work for the purpose

Read#Date
5550001​
28/06/2023​
889910​
29/06/2023​
397840​
30/06/2023​
5550001​
01/07/2023​
5550001​
02/07/2023​
5550001​
03/07/2023​
5550001​
04/07/2023​
889910​
05/08/2023​
889910​
06/08/2023​
889910​
07/08/2023​
889910​
08/08/2023​
397840​
09/08/2023​
397840​
10/07/2023​
397840​
11/07/2023​
397840​
12/07/2023​
 
Back
Top