Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |