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

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

1 ACCEPTED SOLUTION

Accepted Solutions
jbev21 Frequent Visitor
Frequent Visitor

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

Thanks @CheenuSing, 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

View solution in original post

3 REPLIES 3
CheenuSing Super Contributor
Super Contributor

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

Hi @jbev21 

 

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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
jbev21 Frequent Visitor
Frequent Visitor

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

@CheenuSing thanks for your response.

 

I ran into this issue when I used this code:

error_power_bi.png

 

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"

 

jbev21 Frequent Visitor
Frequent Visitor

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

Thanks @CheenuSing, 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

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 351 members 3,701 guests
Please welcome our newest community members: