Forum Discussion

gaikwadaa123's avatar
gaikwadaa123
Helper II
5 years ago
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...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

  • darentengmfs's avatar
    darentengmfs
    5 years ago

    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"

     

darentengmfs's avatar
darentengmfs
Post Prodigy
5 years ago

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's avatar
gaikwadaa123
Helper II
5 years ago

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's avatar
    darentengmfs
    Post Prodigy
    5 years ago

    gaikwadaa123 

     

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

     

    Go to Power Query > Home > Advance Editor.

    • gaikwadaa123's avatar
      gaikwadaa123
      Helper II
      5 years ago

      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's avatar
        darentengmfs
        Post Prodigy
        5 years ago

        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"