Date Table issue

[gaikwadaa123]

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. 

[AlB · Accepted solution]

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 

[gaikwadaa123]

I do not want minute levele data it is going to be always 0. 

[AlB · Accepted solution]

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 

[gaikwadaa123]

Can you elaborate more based on my columns, I am geeting an error with the above code. 

Thank you. 

[AlB]

@gaikwadaa123 

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 

[darentengmfs]

@gaikwadaa123 

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"

[gaikwadaa123]

1. I by mistake cliked on accept as solution. I still need to get this done

[gaikwadaa123]

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

[darentengmfs]

@gaikwadaa123 

Can you share your M Query code for your Date Table?

Go to Power Query > Home > Advance Editor.

[gaikwadaa123]

M query looks like this 

= 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])

[darentengmfs · Accepted solution]

@gaikwadaa123 

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"

[gaikwadaa123]

Hi, Can you help with this,

I would like to combine these two columns forecastdate and forecast hour together as a date time . Thank you. i tried your code for this but it is giving me errors

[AlB]

@gaikwadaa123 

Who are you asking?

Try this in a custom column:

= Date.From([ForecastDate]) & #time([ForecastHour],0,0)

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 

[gaikwadaa123]

This worked like charm. Thank you so much. appreciate it.