Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

max date by month

Hi 

 

 

Without grouping hou we can get max date of each month from stored data file date in query editor?

 

i.e. i have 4 files with date as below

01-01-2020 

01-08-2020

01-15-2020

01-22-2020

01-29-2020

 

For these dates are extracted from file name and i would lile to have only one file date of that month means 01-29-2020 file.

 

i tried with grouping but it taking to much time to refresh the query.

 

Please help

Need Help
Show and Tell
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi, @ Amardeep100115

    The Transform Sample File query will look like this:

    let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA31DdRitWBcwxNUXjmyDwjJDkjfUMUDooUki5jfWNDZB4qB6grFgA=", 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 date}}),

    #"Added YearMonth" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*100 + Date.Month([Date]), Int64.Type),

    #"Added Max Date" =

        Table.AddColumn(#"Added YearMonth", "Max Date",

        each

            let

                varDate = [YearMonth]

            in

            Table.Max(

                Table.SelectRows(#"Added YearMonth", each [YearMonth] = varDate)

                , "Date"

            )[Date]

, type date),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Max Date",{"Date", "Max Date"}),

    #"Added IsMaxDate" = Table.AddColumn(#"Removed Other Columns", "IsMaxDate", each [Date] = [Max Date], type logical),

    #"Filtered Rows" = Table.SelectRows(#"Added IsMaxDate", each ([IsMaxDate] = true)),

    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "IsMaxDate"})

in

#"Removed Columns"

    Here are the source table and the output:

    Here is the demo, please try it: max date by month.pbix

    Best Regards,

    Link Chen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    can you post example of what you doing and what the end result you looking for? for what you say you are importing a folder or kind and want to parse as source the latest file according to its date? 

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Grouping should be the fastest if it is applied in the right way. Remove all unnecessary columns and heavy steps prior to grouping. Sharing a sample file will help solve your issue.

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn


  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , As a new column

    maxx(filter(table, format([date],"MMM-YYYY") = format(earlier([Date]),"MMM-YYYY")),[Date])

     

    But for measure you have to group it my month year

    max(Table[Date]) 

    Display with month year

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi, @ Amardeep100115

    The Transform Sample File query will look like this:

    let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA31DdRitWBcwxNUXjmyDwjJDkjfUMUDooUki5jfWNDZB4qB6grFgA=", 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 date}}),

    #"Added YearMonth" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*100 + Date.Month([Date]), Int64.Type),

    #"Added Max Date" =

        Table.AddColumn(#"Added YearMonth", "Max Date",

        each

            let

                varDate = [YearMonth]

            in

            Table.Max(

                Table.SelectRows(#"Added YearMonth", each [YearMonth] = varDate)

                , "Date"

            )[Date]

, type date),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Max Date",{"Date", "Max Date"}),

    #"Added IsMaxDate" = Table.AddColumn(#"Removed Other Columns", "IsMaxDate", each [Date] = [Max Date], type logical),

    #"Filtered Rows" = Table.SelectRows(#"Added IsMaxDate", each ([IsMaxDate] = true)),

    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "IsMaxDate"})

in

#"Removed Columns"

    Here are the source table and the output:

    Here is the demo, please try it: max date by month.pbix

    Best Regards,

    Link Chen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.