Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Help with creating custom DATIMEZONE column from a sourcename column in text format

Hi All,

 

I have spend a bit of time and i just cant figure out how to get this to work.

 

What i want to do is create a new Datetimezone column from my sourcename column that has example text of : "Friday 10-04-2020 02-00_smb.csv"

 

I have tried first splitting out the _xmb.csv and converting to datetime but powerbi wants the formate of 10/04/2020 and just gives an error.

 

Any help would be appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Hi,

Try this M code.  I am sure there is an easy way to solve this but i could not come up with an alternaitve method.  The reason this code is very convoluted is that the time seperator in your dataset is a - instead of :

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Date/Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date/Time.1", "Date/Time.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date/Time.1", type text}, {"Date/Time.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date/Time.1"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Date/Time.2", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Date/Time.2.1", "Date/Time.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date/Time.2.1", type text}, {"Date/Time.2.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Date/Time.2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns1", "Date/Time.2.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Date/Time.2.1.1", "Date/Time.2.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Date/Time.2.1.1", type date}, {"Date/Time.2.1.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","-",":",Replacer.ReplaceText,{"Date/Time.2.1.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Date/Time.2.1.1", type text}}, "en-IN"),{"Date/Time.2.1.1", "Date/Time.2.1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetimezone}})
in
    #"Changed Type4"

 Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

You need to convert it to a standard date format, and the dash in the time is messing it up. This will do the trick I believe. It converted it to this date time zone value. The -7 is mine, which is local. You would need to use the DateTimeZone.SwitchTimeZone function if the timezone it uses doesn't work for you. Just add/subtract hours.

 

2020-04-16 19_11_52-Untitled - Power Query Editor.png

    Text.Start(
        Text.Middle([Date],
            Text.PositionOf([Date]," ") + 1,
            Text.Length([Date]) - Text.PositionOf([Date],"_") + Text.PositionOf([Date]," ") +2
        ),
        13
    ) 
    & ":" & 
    Text.AfterDelimiter(
        Text.Middle([Date],
            Text.PositionOf([Date]," ") + 1,
            Text.Length([Date]) - Text.PositionOf([Date],"_") + Text.PositionOf([Date]," ") +2),"-",2
        )
    )

 

Or you can see the full M code here, just put this in a blank query

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKTEmsVDA00DUw0TUyMDJQMDDSNTCIL85N0ksuLlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Just The Date", each 
    Text.Start(
        Text.Middle([Date],
            Text.PositionOf([Date]," ") + 1,
            Text.Length([Date]) - Text.PositionOf([Date],"_") + Text.PositionOf([Date]," ") +2
        ),
        13
    ) 
    & ":" & 
    Text.AfterDelimiter(
        Text.Middle([Date],
            Text.PositionOf([Date]," ") + 1,
            Text.Length([Date]) - Text.PositionOf([Date],"_") + Text.PositionOf([Date]," ") +2),"-",2
        )
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Just The Date", type datetime}})
in
    #"Changed Type1"

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, use the Split column feature to seperate at the first space, last _ and last space.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi I already tried that and have format of  '10-04-2020 02-00'  , but converting column to datetime still gives an error

Hi,

Try this M code.  I am sure there is an easy way to solve this but i could not come up with an alternaitve method.  The reason this code is very convoluted is that the time seperator in your dataset is a - instead of :

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Date/Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date/Time.1", "Date/Time.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date/Time.1", type text}, {"Date/Time.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date/Time.1"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Date/Time.2", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Date/Time.2.1", "Date/Time.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date/Time.2.1", type text}, {"Date/Time.2.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Date/Time.2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns1", "Date/Time.2.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Date/Time.2.1.1", "Date/Time.2.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Date/Time.2.1.1", type date}, {"Date/Time.2.1.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","-",":",Replacer.ReplaceText,{"Date/Time.2.1.2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Date/Time.2.1.1", type text}}, "en-IN"),{"Date/Time.2.1.1", "Date/Time.2.1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetimezone}})
in
    #"Changed Type4"

 Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi, Just tried this and worked with a little bit of playing around. Decided to just use datetime instead of datetimezone as it set it as local timezone but wanted it to be utc 0

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.