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

[swys42]

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

[edhans]

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.

    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"

[Ashish_Mathur]

Hi,

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

[swys42]

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

[Ashish_Mathur · 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.

[swys42]

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