Local time in Power Query

[FlorianF]

Hi guys,

I'm pretty new to Power BI. 

My problem is, that my report in the web portal shows the wrong date/time because of local time and daylight time.

How can I calculate the date/time in Power Query, tbat my report shows the right date/time.

Thank you! 

[v-yalanwu-msft]

Hi, @FlorianF ;

In theory, if a symbol or expression is wrong, you test the code one by one.
I looked at it and your else has no sign and no value behind it, not sure if it's this error;
Secondly, the final sign question.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FlorianF]

Hey Yalan,

thanks. I deleted the last sign before the in.

But is the else state not the following code rows? I looked like this as long as it works, and I put in the Daylight-Code after.

And when I removed the last comma, I got the issue in the if.

This is the whole code:

let
Quelle = Table.NestedJoin(lead, {"result.customAttributes.attributeDefinitionId"}, customAtrributes, {"result.id"}, "customAtrributes", JoinKind.LeftOuter),
#"Erweiterte customAtrributes" = Table.ExpandTableColumn(Quelle, "customAtrributes", {"result.attributeLabels.labelText"}, {"customAtrributes.result.attributeLabels.labelText"}),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Erweiterte customAtrributes",{"result.leadNumber", "result.company", "result.customAttributes.attributeDefinitionId", "customAtrributes.result.attributeLabels.labelText", "result.customAttributes.booleanValue", "result.customAttributes.selectedValueId", "result.customAttributes.stringValue", "result.salesStageName"}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",{{"result.customAttributes.dateValue", Int64.Type}}),
#"CustomAttributID löschen" = Table.RemoveColumns(#"Geänderter Typ",{"result.customAttributes.attributeDefinitionId"}),
#"Filter auf Rabatt bis" = Table.SelectRows(#"CustomAttributID löschen", each ([customAtrributes.result.attributeLabels.labelText] = "ÜH-Tool: Rabatt bis:")),
#"CustomAttribute Spalten löschen" = Table.RemoveColumns(#"Filter auf Rabatt bis",{"result.customAttributes.booleanValue", "result.customAttributes.selectedValueId", "result.customAttributes.stringValue", "result.salesStageName"}),
#"Rabatt in Datum umrechnen" = Table.AddColumn(#"CustomAttribute Spalten löschen", "Rabatt bis", each if [result.customAttributes.dateValue] is null then
""
else

//DateTimeZone.ToLocal(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [result.customAttributes.dateValue]/1000),0))),

#"Format Rabatt bis als Datum" = Table.TransformColumnTypes(#"Rabatt in Datum umrechnen",{{"Rabatt bis", type date}}),

#"Start Sommerzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),3,25), Day.Monday),
#"Start Winterzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),10,25), Day.Monday),

UTCOffset = if #"Format Rabatt bis als Datum" > #"Start Winterzeit" & #time(3,0,0) or #"Format Rabatt bis als Datum" < #"Start Sommerzeit" & #time(2,0,0)
then 1 else 2,

#"Rabatt ohne Zeitzone" = Table.AddColumn(#"Typ Datum", "Rabatt bis", 
each DateTime.From(DateTimeZone.RemoveZone([Rabatt bis]) + #duration(0,UTCOffset,0,0))
),

#"Entfernte Spalten" = Table.RemoveColumns(#"Format Rabatt bis als Datum",{"customAtrributes.result.attributeLabels.labelText", "result.customAttributes.dateValue", "Erstelldatum"}),
#"Spalten umbenennen" = Table.RenameColumns(#"Entfernte Spalten",{{"result.leadNumber", "Nummer"}, {"result.company", "Interessent"}}),
#"Spalte mit Link hinzufügen" = Table.AddColumn(#"Spalten umbenennen", "Link zum Interessent", each "https://ffbc.weclapp.com/webapp/view/party/PartyDetail.page?mid=29&entityId=" & [result.id]),
#"Format Link als Text" = Table.TransformColumnTypes(#"Spalte mit Link hinzufügen",{{"Link zum Interessent", type text}}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Format Link als Text", each ([Rabatt bis] <> null)),
#"Umbenannte Spalten" = Table.RenameColumns(#"Gefilterte Zeilen",{{"result.id", "Id"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Rabatt bis", type datetimezone}}),
#"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ1",{{"Rabatt bis", Order.Ascending}})
in
#"Sortierte Zeilen"

[v-yalanwu-msft · Accepted solution]

Hi, @FlorianF ;

I know it show error in if, but if we delete this steps; the error show "in"

If i change to this, it's ok .so i think the problem is after "else"

And if i add a ")" ,the error is disappear. As to why the if error is displayed there, because the ones that follow else belong to the substatement and cannot be called before.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FlorianF]

Hi Yalan,

I think I got the right code now for German time and want it to implement it in one of my queries so I copied the relevant code row.

But I got an error on the if statement which say, "literal expected". What does this mean? I do not find something understandable for me 😉

let
Quelle = Table.NestedJoin(lead, {"result.customAttributes.attributeDefinitionId"}, customAtrributes, {"result.id"}, "customAtrributes", JoinKind.LeftOuter),
#"Erweiterte customAtrributes" = Table.ExpandTableColumn(Quelle, "customAtrributes", {"result.attributeLabels.labelText"}, {"customAtrributes.result.attributeLabels.labelText"}),
#"Neu angeordnete Spalten" = Table.ReorderColumns(#"Erweiterte customAtrributes",{"result.leadNumber", "result.company", "result.customAttributes.attributeDefinitionId", "customAtrributes.result.attributeLabels.labelText", "result.customAttributes.booleanValue", "result.customAttributes.selectedValueId", "result.customAttributes.stringValue", "result.salesStageName"}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",{{"result.customAttributes.dateValue", Int64.Type}}),
#"CustomAttributID löschen" = Table.RemoveColumns(#"Geänderter Typ",{"result.customAttributes.attributeDefinitionId"}),
#"Filter auf Rabatt bis" = Table.SelectRows(#"CustomAttributID löschen", each ([customAtrributes.result.attributeLabels.labelText] = "ÜH-Tool: Rabatt bis:")),
#"CustomAttribute Spalten löschen" = Table.RemoveColumns(#"Filter auf Rabatt bis",{"result.customAttributes.booleanValue", "result.customAttributes.selectedValueId", "result.customAttributes.stringValue", "result.salesStageName"}),
#"Rabatt in Datum umrechnen" = Table.AddColumn(#"CustomAttribute Spalten löschen", "Rabatt bis", each if [result.customAttributes.dateValue] is null then
""
else

//DateTimeZone.ToLocal(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [result.customAttributes.dateValue]/1000),0))),

#"Format Rabatt bis als Datum" = Table.TransformColumnTypes(#"Rabatt in Datum umrechnen",{{"Rabatt bis", type date}}),

#"Start Sommerzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),3,25), Day.Monday),
#"Start Winterzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),10,25), Day.Monday),

UTCOffset = if #"Format Rabatt bis als Datum" > #"Start Winterzeit" & #time(3,0,0) or #"Format Rabatt bis als Datum" < #"Start Sommerzeit" & #time(2,0,0)
then 1 else 2,

#"Rabatt ohne Zeitzone" = Table.AddColumn(#"Typ Datum", "Rabatt bis", 
each DateTime.From(DateTimeZone.RemoveZone([Rabatt bis]) + #duration(0,UTCOffset,0,0))
),

#"Entfernte Spalten" = Table.RemoveColumns(#"Format Rabatt bis als Datum",{"customAtrributes.result.attributeLabels.labelText", "result.customAttributes.dateValue", "Erstelldatum"}),
#"Spalten umbenennen" = Table.RenameColumns(#"Entfernte Spalten",{{"result.leadNumber", "Nummer"}, {"result.company", "Interessent"}}),
#"Spalte mit Link hinzufügen" = Table.AddColumn(#"Spalten umbenennen", "Link zum Interessent", each "https://ffbc.weclapp.com/webapp/view/party/PartyDetail.page?mid=29&entityId=" & [result.id]),
#"Format Link als Text" = Table.TransformColumnTypes(#"Spalte mit Link hinzufügen",{{"Link zum Interessent", type text}}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Format Link als Text", each ([Rabatt bis] <> null)),
#"Umbenannte Spalten" = Table.RenameColumns(#"Gefilterte Zeilen",{{"result.id", "Id"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Rabatt bis", type datetimezone}}),
#"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ1",{{"Rabatt bis", Order.Ascending}}),
in
#"Sortierte Zeilen"

[v-yalanwu-msft]

Hi, @FlorianF ;

Since daylight saving time in Germany is not very clear, I saw a good post to help you solve daylight saving time problems even more, relatively clear.

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),

isSummerTime =	(date = SecondSundayOfMarch and time >= #time(1,0,0))
	        or
		(date > SecondSundayOfMarch and date < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

MDT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    MDT

Daylight Savings Time Changes in Power Query - YouTube

Solved: Convert Date/Time in UTC to Local Time with Daylig... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @FlorianF ;

1.In Power Query, Click New Source > Blank Query

2.Add function == DateTimeZone.LocalNow()

3.Select Query, Click Transform > To Table. This will create a DateTime in UTC format.

4. Change Data Type

5.Add custom Column DateTime_AEST. This will then make the calculation to convert time to AEST.

=DateTime.From(DateTimeZone.RemoveZone([Column1]) + #duration(0,10,0,0))

6. To take into account daylight saving, Click Advanced Editor.

The whole m code.

let
    Source = DateTimeZone.LocalNow(),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetimezone}}),
    DayLightSavingStarts = Date.StartOfWeek(#date(Date.Year (DateTime.LocalNow()),4,7), Day.Sunday),
    DayLightSavingEnds = Date.StartOfWeek(#date(Date.Year (DateTime.LocalNow()),10,7), Day.Sunday),
    CurrentDateTime = DateTimeZone.RemoveZone (DateTimeZone.UtcNow()),
    UTCOffset = if CurrentDateTime > DayLightSavingEnds & #time(3,0,0) or CurrentDateTime < DayLightSavingStarts & #time(2,0,0)
    then 11 else 10,

    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime_AEST", 
    each DateTime.From(DateTimeZone.RemoveZone([Column1]) + #duration(0,UTCOffset,0,0)))
in
    #"Added Custom"

Once you save the report and publish to service you will see that the correct date reflected.

DST Refresh Date Function Power BI Service-daylight-savings-time (p3adaptive.com)

Solved: Handling local time zone with Daylight Savings in ... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FlorianF]

Hey Yalan,

thanks a lot for your support.

I adjusted the code to my specifics in Germany, but it calculates wrong. Seem that I not finally understand the code. Know that I'm pretty new to PQ and M 😉

This is the code I use now

let
Quelle = DateTimeZone.LocalNow(),
#"In Tabelle konvertiert" = #table(1, {{Quelle}}),
#"Typ Datum" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", type datetimezone}}),
#"Start Sommerzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),3,22), Day.Monday),
#"Start Winterzeit" = Date.EndOfWeek(#date(Date.Year (DateTime.LocalNow()),10,28), Day.Monday),
#"Aktuelle Zeit" = DateTimeZone.RemoveZone (DateTimeZone.UtcNow()),
UTCOffset = if #"Aktuelle Zeit" > #"Start Winterzeit" & #time(3,0,0) or #"Aktuelle Zeit" < #"Start Sommerzeit" & #time(2,0,0)
then 2 else 1,

#"Lokale Zeit" = Table.AddColumn(#"Typ Datum", "Lokale Zeit", 
each DateTime.From(DateTimeZone.RemoveZone([Column1]) + #duration(0,UTCOffset,0,0))),
#"Umbenannte Spalten" = Table.RenameColumns(#"Lokale Zeit",{{"Column1", "Lokale Zeit mit Zeitzone"}})
in
#"Umbenannte Spalten"

In Germany, it is now 25.11.2022 6:39.

But it calculates 8:39.

And did I make to adjust to the last Sunday in a month, correct?

Thanks!!