Time zone setting between sharepoint List and PBI

[jeongkim]

Hi, above is the time added in sharepoint list and this will go to PBI dataset. 
List is already set up with GMT +9 (Korea time) but how to sync up PBI time zone? 

+I've changed the setting but still the same...

[Ahmed-Elfeel]

Hi @jeongkim,

In Power BI Desktop:

• Go to Power Query Editor

• Select your date/time column

• Go to Transform tab

• Use Date/Time → Change Type → Using Locale

• Set data type to "Date/Time" and select "Korean" locale

Add Time Zone Conversion:

// Add custom column to convert timezone
Table.TransformColumns(#"Previous Step", {{"YourDateTimeColumn", each DateTimeZone.From(_) + #duration(0,9,0,0)}})

Or You can use the easiest approach (Data Source Settings):

• In Power Query

• Right click your SharePoint data source

• Select (Data source settings)

• Check if there's timezone configuration

• Apply Korean timezone settings

If the above don't work create a calculated column:

Adjusted Time = 
    'Your Table'[Approved Time] + TIME(9,0,0)

Dont forget to Refresh your data 😅❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[jeongkim]

Hi,

In Query, I chose Using locale and selected Korean(was English) but it only changes language not +9 time. 

[Ahmed-Elfeel · Accepted solution]

Hi @jeongkim,

If you want the simplest way you can try this:

In Power Query Editor:

• Select your ApprovedTime column

• Go to Add Column tab

• Click Date/Time → Date/Time → Add Hours

• Enter 9 to add 9 hours for Korea time

Or Use Advanced Editor:

Replace your current step with this M code:

let
    Source = ... // your previous steps,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ApprovedTime", type datetime}}),
    #"Added Custom" = Table.TransformColumns(#"Changed Type", {{"ApprovedTime", each DateTime.From(_) + #duration(0,9,0,0)}})
in
    #"Added Custom"

🚀You can Also Use Custom Column: 

Add Custom Column:

• Go to Add Column → Custom Column

• Name: ApprovedTime_KST

• Formula: = [ApprovedTime] + #duration(0,9,0,0)

Or Just Compelete your Timezone Conversion:

let
    Source = ... // your previous steps,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ApprovedTime", type datetime}}),
    #"Converted to KST" = Table.TransformColumns(#"Changed Type", {{"ApprovedTime", each 
        if _ is null then null
        else DateTime.From(DateTimeZone.RemoveZone(_)) + #duration(0,9,0,0)
    }})
in
    #"Converted to KST"

You can try it all starting with Approach No.1 (Simplest Method)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.