Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

CheenuSing
Community Champion
Community Champion

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

 

 

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

Proud to be a Datanaut!
Anonymous
Not applicable

@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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.