Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Min and max in M (Power Query) for date table

I have an existing query that I need to update the max date to read min of the max date in another table or last day of the current month. Sound confusing let me explain further.

 

My current query looks like this (I didn't write it, I used it from someone else):

let ChangedType = Table.TransformColumnTypes(wh_Actual_Movements,{{"Date", type date}}), 
MaxDate = Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MinDate = Record.Field(Table.Min(ChangedType, "Date"),"Date"), 
DaysElapsed = Number.From(MaxDate-MinDate), 
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), 
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(),  
{"Date"}, null, ExtraValues.Error), 
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}), 
InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number), 
InsertedMonth = Table.AddColumn(InsertedDay, "Month", each
Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(InsertedYear, "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Jun Fin Year" = Table.AddColumn(#"Inserted Month Name", "Jun Fin Year", each if [Month] >= 7 then [Year] + 1
else [Year]),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Jun Fin Year",{{"Jun Fin Year", Int64.Type}}),
    #"Inserted Month Short" = Table.AddColumn(#"Changed Type", "Month Short", each Text.Start([Month Name], 3)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month Short",{{"Month Short", type text}}),
    #"Inserted End of Month" = Table.AddColumn(#"Changed Type1", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Insert Month_Year_Name" = Table.AddColumn(#"Inserted End of Month", "Month_Year_Name", each [Month Short] & "-" & Text.From([Year])),
    #"Inserted End of Week" = Table.AddColumn(#"Insert Month_Year_Name", "End of Week", each Date.EndOfWeek([Date]), type date)
in
	#"Inserted End of Week"

The line I want to update is:

MaxDate = Record.Field(Table.Max(ChangedType, "Date"),"Date"),

 

I would like it read something like (excuse the excel terms but financial modelling is my trade):

MIN( EOMONTH( NOW(), 0), Record.Field(Table.Max(ChangedType, "Date"),"Date") )

 

Can you please assist?

Thanks in Advance

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks Anonymous, you put me on the right track. I did need to make the NOW date into a list otherwise it would not work.

     

    The final solution took a minor tweak but this is what I landed with:

     

    The lines that I updated/added

    EoMonthNow =  List.Dates(Date.EndOfMonth(Date.From(DateTime.LocalNow())), 1, #duration(0,0,0,0)),
MaxInputDate =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MaxDate = List.Min(EoMonthNow, MaxInputDate),

    The full query

    let ChangedType = Table.TransformColumnTypes(wh_Actual_Movements,{{"Date", type date}}),
EoMonthNow =  List.Dates(Date.EndOfMonth(Date.From(DateTime.LocalNow())), 1, #duration(0,0,0,0)),
MaxInputDate =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MaxDate = List.Min(EoMonthNow, MaxInputDate), 
MinDate = Record.Field(Table.Min(ChangedType, "Date"),"Date"), 
DaysElapsed = Number.From(MaxDate-MinDate), 
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), 
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(),  
{"Date"}, null, ExtraValues.Error), 
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}), 
InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number), 
InsertedMonth = Table.AddColumn(InsertedDay, "Month", each
Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(InsertedYear, "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Jun Fin Year" = Table.AddColumn(#"Inserted Month Name", "Jun Fin Year", each if [Month] >= 7 then [Year] + 1
else [Year]),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Jun Fin Year",{{"Jun Fin Year", Int64.Type}}),
    #"Inserted Month Short" = Table.AddColumn(#"Changed Type", "Month Short", each Text.Start([Month Name], 3)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month Short",{{"Month Short", type text}}),
    #"Inserted End of Month" = Table.AddColumn(#"Changed Type1", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Insert Month_Year_Name" = Table.AddColumn(#"Inserted End of Month", "Month_Year_Name", each [Month Short] & "-" & Text.From([Year])),
    #"Inserted End of Week" = Table.AddColumn(#"Insert Month_Year_Name", "End of Week", each Date.EndOfWeek([Date]), type date)
in
	#"Inserted End of Week"

    Thanks for your help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous 

     

    You could try replace that with the 3 steps

     

    1. Step1 =  Date.EndOfMonth(Date.From(DateTime.LocalNow())),

    2.Step2 =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 

    3. MaxDate = List.Min({Step1,Step2})

     

    Cheers

     

    CheenuSing

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous thanks for your response.

       

      I ran into this issue when I used this code:

       

      The query looks like this now:

      let ChangedType = Table.TransformColumnTypes(wh_Actual_Movements,{{"Date", type date}}),
Step1 =  Date.EndOfMonth(Date.From(DateTime.LocalNow())),
Step2 =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MaxDate = List.Min(Step1, Step2), 
MinDate = Record.Field(Table.Min(ChangedType, "Date"),"Date"), 
DaysElapsed = Number.From(MaxDate-MinDate), 
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), 
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(),  
{"Date"}, null, ExtraValues.Error), 
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}), 
InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number), 
InsertedMonth = Table.AddColumn(InsertedDay, "Month", each
Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(InsertedYear, "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Jun Fin Year" = Table.AddColumn(#"Inserted Month Name", "Jun Fin Year", each if [Month] >= 7 then [Year] + 1
else [Year]),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Jun Fin Year",{{"Jun Fin Year", Int64.Type}}),
    #"Inserted Month Short" = Table.AddColumn(#"Changed Type", "Month Short", each Text.Start([Month Name], 3)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month Short",{{"Month Short", type text}}),
    #"Inserted End of Month" = Table.AddColumn(#"Changed Type1", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Insert Month_Year_Name" = Table.AddColumn(#"Inserted End of Month", "Month_Year_Name", each [Month Short] & "-" & Text.From([Year])),
    #"Inserted End of Week" = Table.AddColumn(#"Insert Month_Year_Name", "End of Week", each Date.EndOfWeek([Date]), type date)
in
	#"Inserted End of Week"

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Anonymous, you put me on the right track. I did need to make the NOW date into a list otherwise it would not work.

     

    The final solution took a minor tweak but this is what I landed with:

     

    The lines that I updated/added

    EoMonthNow =  List.Dates(Date.EndOfMonth(Date.From(DateTime.LocalNow())), 1, #duration(0,0,0,0)),
MaxInputDate =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MaxDate = List.Min(EoMonthNow, MaxInputDate),

    The full query

    let ChangedType = Table.TransformColumnTypes(wh_Actual_Movements,{{"Date", type date}}),
EoMonthNow =  List.Dates(Date.EndOfMonth(Date.From(DateTime.LocalNow())), 1, #duration(0,0,0,0)),
MaxInputDate =  Record.Field(Table.Max(ChangedType, "Date"),"Date"), 
MaxDate = List.Min(EoMonthNow, MaxInputDate), 
MinDate = Record.Field(Table.Min(ChangedType, "Date"),"Date"), 
DaysElapsed = Number.From(MaxDate-MinDate), 
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), 
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(),  
{"Date"}, null, ExtraValues.Error), 
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}), 
InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number), 
InsertedMonth = Table.AddColumn(InsertedDay, "Month", each
Date.Month([Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(InsertedYear, "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Jun Fin Year" = Table.AddColumn(#"Inserted Month Name", "Jun Fin Year", each if [Month] >= 7 then [Year] + 1
else [Year]),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Jun Fin Year",{{"Jun Fin Year", Int64.Type}}),
    #"Inserted Month Short" = Table.AddColumn(#"Changed Type", "Month Short", each Text.Start([Month Name], 3)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month Short",{{"Month Short", type text}}),
    #"Inserted End of Month" = Table.AddColumn(#"Changed Type1", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Insert Month_Year_Name" = Table.AddColumn(#"Inserted End of Month", "Month_Year_Name", each [Month Short] & "-" & Text.From([Year])),
    #"Inserted End of Week" = Table.AddColumn(#"Insert Month_Year_Name", "End of Week", each Date.EndOfWeek([Date]), type date)
in
	#"Inserted End of Week"

    Thanks for your help