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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.