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

Local time in Power Query

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! 

1 ACCEPTED SOLUTION

Hi, @FlorianF ;

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

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

vyalanwumsft_1-1669625152067.pngvyalanwumsft_2-1669625352339.png

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.

 

View solution in original post

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1669617760847.png

vyalanwumsft_1-1669617852205.png

vyalanwumsft_2-1669617901032.png


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.

 

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.

 

Unbenannt.JPG

 

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"


 

Hi, @FlorianF ;

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

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

vyalanwumsft_1-1669625152067.pngvyalanwumsft_2-1669625352339.png

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
Frequent Visitor

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
Community Support
Community Support

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
Community Support
Community Support

Hi, @FlorianF ;

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

2.Add function == DateTimeZone.LocalNow()

vyalanwumsft_0-1669170371320.png

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

4. Change Data Type

vyalanwumsft_1-1669170473821.png

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))

vyalanwumsft_2-1669170538260.png

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

vyalanwumsft_3-1669170750574.png

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.

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!!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors