Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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.
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,
In the Query Editor, use the Split column feature to seperate at the first space, last _ and last space.
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
65 | |
55 |