Forum Discussion
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:00:00:PM
Can somebody help to send me solution by using column names in my table(Image)
Thank you.
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"
13 Replies
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
Can you elaborate more based on my columns, I am geeting an error with the above code.
Thank you.
Not much to elaborate on
= #date([DbYear],[DBMonth],[DBDay]) & #time([DBHour],0,0)Just use your columns names (case sensitive)
Next time please share the table in tex-tabular format rather than (or in addition to) a screen cap. Otherwise the contents cannnot be copied
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
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 am getting error with the source, How do i have to put exact source, And do i have to do this on blank query?
Can you littile elaborate on the steps, I am new to power BI
Can you share your M Query code for your Date Table?
Go to Power Query > Home > Advance Editor.
- I by mistake cliked on accept as solution. I still need to get this done
I do not want minute levele data it is going to be always 0.
