Forum Discussion
Solved
Date Table issue
Hello, I have the table like below, it has three columns DbYear Db Month, Db Day and Db Hour. I would like to create the column which will make it DateTime table in formate 1/20/2021 3:0...
Hi gaikwadaa123
Try this in a custom column, using your column names
#date([Year],[Month],[Day]) & #time([Hour],0,0)then convert to datetime if necessary
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Do you have other steps, if so, paste all of your advance editor code. If you don't have any other steps in your query, do this.
let
Source = Sql.Database("Ops1-db", "NCEMC", [Query="SELECT [StationID]#(lf) ,[DbYear]#(lf) ,[DBMonth]#(lf) ,[DBDay]#(lf) ,[DBHour]#(lf) ,[DbTimeMode]#(lf) ,[DBDryBulb]#(lf) #(lf) FROM [NCEMC].[dbo].[WeatherData]#(lf) where StationID ='29' and DbYear ='2021'", CreateNavigationProperties=false, HierarchicalNavigation=true]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DbYear", Int64.Type}, {"DBMonth", Int64.Type}, {"DBDay", Int64.Type}, {"DBHour", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"DBMonth", type text}, {"DBDay", type text}, {"DbYear", type text}}, "en-US"),{"DBMonth", "DBDay", "DbYear"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "DBMinute", each ":00"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"DBHour", type text}}, "en-US"),{"DBHour", "DBMinute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Time"),
#"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Date", type text}}, "en-US"),{"Date", "Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns2",{{"DateTime", type datetime}})
in
#"Changed Type2"
Please try my M code. Just change the Source to your source. I have named the columns as your column names. Note that I do not see a minute column in your table, hence, I created my own minute column for this to work.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUQJhIwMwIzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DbYear = _t, DBMonth = _t, DBDay = _t, DBHour = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DbYear", Int64.Type}, {"DBMonth", Int64.Type}, {"DBDay", Int64.Type}, {"DBHour", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"DBMonth", type text}, {"DBDay", type text}, {"DbYear", type text}}, "en-US"),{"DBMonth", "DBDay", "DbYear"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "DBMinute", each ":00"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"DBHour", type text}}, "en-US"),{"DBHour", "DBMinute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Time"),
#"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Date", type text}}, "en-US"),{"Date", "Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns2",{{"DateTime", type datetime}})
in
#"Changed Type2"
- I by mistake cliked on accept as solution. I still need to get this done