Convert Date/Time in UTC to Local Time with Daylight savings

No, it is using the year of the date you provided to determine the first Sunday of April and October.  My concern still is that on the first Sunday of April that it may not be doing the shift at the exact hour for DST.  That would mean that only that specific period of 10 or 11 hours would be effected by this.

You can see data by calling the function with the below code.  You can control the date range you want by modifying the StartDate and EndDate.

This will let you look at the specific days that will be effected for the time period you are looking at, and also research whether the times are shifting at the right time or not.  I think the IsSummerTime formula may need to be tweaked as I believe it is using the UTC time to determine the DST, not the AST time.

Let me know if you need help with that part.  No matter what, I think this has you a lot closer except for those 11 hours or so between the UTC and AST times of DST.

---------------------------------------------------------------

let
StartDate = #date(2019, 1, 1),
EndDate = #date(2021, 12, 31),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount*24,#duration(0,1,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(ChangedType,{{"Column1", "DateUTC"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "DayOfWeekName", each Date.DayOfWeekName( [DateUTC] )),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "FilterDates", each if ( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 3, 31 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 4, 8 )) or
( DateTime.Date( [DateUTC] ) >= #date( Date.Year( DateTime.Date ( [DateUTC] )), 9, 30 ) and DateTime.Date( [DateUTC] ) <= #date( Date.Year( DateTime.Date ( [DateUTC] )), 10, 8 )) then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([FilterDates] = "Y") and ([DayOfWeekName] = "Saturday" or [DayOfWeekName] = "Sunday")),
#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "DateAST", each AST([DateUTC]), type datetime)
in
#"Invoked Custom Function"

Here is the updated code.  The part trying to convert the isSummerTime to a number was causing an error, so I added another line that checks whether it is null, and and if it is, uses a value of zero.  It worked fine for me.

Please make sure you validate the timing on those DST periods per the code I gave above.  I am concerned still that on those days it changes, there will be a period of 10 or 11 hours that will have the wrong time.

(datetimecolumn as nullable datetime) =>

let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

firstSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 7), Day.Sunday),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),

isSummerTime =	
		(date = firstSundayOfOctober and time >= #time(2,0,0)) 
	    or
		(date > firstSundayOfOctober) 
		or
		(date < firstSundayOfApril)  
		or 
		(date = firstSundayOfApril and time >= #time(3,0,0)), 

timeZoneAdj = if datetimecolumn is null then 0 else Number.From(isSummerTime),
timeZone = 10 + timeZoneAdj,
										   
AEST = 
            DateTime.From(datetimecolumn) + #duration(0, timeZone, 0, 0) 														    
in
    AEST