Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
@CheenuSing 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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |