Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
11 months ago
Solved

Time zone setting between sharepoint List and PBI

 

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

 

 

General Comment
Need Help
Show and Tell
Tutorial Requests
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    11 months ago

    Hi Anonymous,

    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.

3 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    11 months ago

    Hi Anonymous,

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      11 months ago

      Hi,

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

       

       

      • Ahmed-Elfeel's avatar
        Ahmed-Elfeel
        Super User
        11 months ago

        Hi Anonymous,

        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.