cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA
Solution Sage
Solution Sage

Keeping the last date of the month in Query Editor

Hi all,

What should be a quick question here - I have a two-column table, a date/time/zone stamp and a member count (example pics below). All I need to do is keep the last date of each month, such that the resulting table has one row for each month, and the final member count for that month. Any help? 

 

The first few rows for April 2021:

RMDNA_0-1628026137963.png

 

The final few rows for April 2021. I just need a row (April 2021) with the value 22,083. This pattern continues into August.

RMDNA_1-1628026284276.png

 

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

let
    Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    #"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
    new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
    #"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns", each ([new_membercount] <> 0)),
    #"Grouped by month" = Table.Group(#"Filtered Rows2", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
    Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
    Extraction

View solution in original post

11 REPLIES 11
CNENFRNL
Community Champion
Community Champion

Easy one, group the table by month and extract the desired rows.

This solution works regardless the last record falls on the end of the month.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7JCcAwDETRVoLOBm1eErdi3H8bTmKkgHKdx8AfAzKyoJDwUTu1TgQJmGCmlzSSOOVI6lQiZacaqRgpRaqRmlPbVJC3nPds7Ub2+kx+drnpY3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [new_createdon = _t, new_membercount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"new_createdon", type datetime}, {"new_membercount", Int64.Type}}),

    #"Grouped by month" = Table.Group(#"Changed Type", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
    Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
    Extraction

 

 

Screenshot 2021-08-04 005134.png

Hi @CNENFRNL - this looks like what I'll need, since I still want values for the most recent date in a month that's still in progress. However, I'm not experienced enough yet to apply your code in the advanced editor to a non-static source. Given the anonymized query below, could you help me to add your code? Thanks!

 

 

Spoiler
let
    Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    #"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
    new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
    #"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"new_createdon", Order.Ascending}})),
    #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([new_membercount] <> 0))
in
    #"Filtered Rows2"

 

CNENFRNL
Community Champion
Community Champion

let
    Source = Cds.Entities("anonymous source", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    #"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
    new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
    #"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "anonymous filter")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns", each ([new_membercount] <> 0)),
    #"Grouped by month" = Table.Group(#"Filtered Rows2", "new_createdon", {"Grouped by Mo", each Table.Sort(_, {"new_createdon", Order.Descending}){0}}, 0, (x,y) => Number.From(Date.Month(x)<>Date.Month(y))),
    Extraction = Table.FromRecords(#"Grouped by month"[Grouped by Mo])
in
    Extraction

View solution in original post

@CNENFRNL Nifty, my Table.Group skillz don't have nearly the drip yours do...


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

edhans
Super User
Super User

Hey @RMDNA just use this as your filter in a Table.SelectRows statement. Filter your date by anything just to get the statement written, then replace the filter condition in the formula bar with this:

 

DateTime.Date([Date]) = Date.EndOfMonth(DateTime.Date([Date]))

 

It compares the date only of the end of the month for that date and keeps it. Full code with sample data is here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3MtQ3MjAyVDA0szIwUIrVAYkZG0DEDCxgYsb6xlB1JgghQ1OoVkuwWCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Changed Type", 
            each 
                (DateTime.Date([Date]) = Date.EndOfMonth(DateTime.Date([Date])))
            )
in
    #"Filtered Rows"

 

 

If you need more help, provide sample data we can use. Cannot use images. Good for expected results, not for input.


How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans - this would work well if not for two things: I need the most recent data for a month still in progress (e.g. whatever the value is for today, 8/4), and this takes the last day of the month, but since it's a date/time field, there's 20 rows for 4/30.

 

If there's any way to adapt for this, let me know! 

RMDNA
Solution Sage
Solution Sage

Hi @edhans (and also @Greg_Deckler and @CNENFRNL) - sorry, I'm juggling threads from several of you right now.

Download link for a PBIX with the data is here.

Just for clarity's sake: I need to get the value of "new_membercount" from the last date/time stamp in a month, as well as the same value for the most recent day of the current month. For example, the EndOfMonth solution that edhans originally provided gave the following:

RMDNA_0-1628104838705.png

However, I need only that last value for 6/30. Additionally, you'll see there's no data for 8/4 (today), which is the latest date of this current month.  

An anonymized version of the query is as follows, so if you provide code, please let me know how it would fit into this query:

Spoiler
let
    Source = Cds.Entities("redacted datasource", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    #"Filtered Rows" = Table.SelectRows(entities, each ([SchemaName] = "new_segmentupdate")),
    new_segmentupdates = #"Filtered Rows"{[EntitySetName="new_segmentupdates"]}[Data],
    #"Filtered Rows1" = Table.SelectRows(new_segmentupdates, each ([new_name] = "redacted filter")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"new_createdon", "new_membercount"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"new_createdon", Order.Ascending}})),
    #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([new_membercount] <> 0))
in
    #"Filtered Rows2"


Thanks!  

 

Can you post some data per the links below? Very hard to type stuff in and try to replicate your scenario.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Ah @edhans, I am a fool, a fool I tell you, for forgetting there is an EndOfMonth function in Power Query!!


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

There are over 800 functions in Power Query @Greg_Deckler - no one can possibly remember them all! 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@RMDNA Not as easy as you might think but here is one way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5BCsAgDETRq5SsBZNoYutVxPtfo2KrLbOdx8BvjXIUjcoqh1culZkCCfUwJaHokoySlhhKXuIo9kpiFAcpW8oUi/LAOdZ/tO3LJwpybRnR/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [new_createdon = _t, new_membercount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"new_createdon", type datetime}, {"new_membercount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Year([new_createdon]) * 100 + Date.Month([new_createdon])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Group(#"Added Custom", {"Custom"}, {{"max", each List.Max([new_createdon]), type nullable datetime}})),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"max"}, {"Custom.1.max"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [new_createdon] = [Custom.1.max] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.1] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1.max", "Custom.1"})
in
    #"Removed Columns"

 

@ImkeF @edhans and @mahoneypat might know of a better way.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors