• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create custom column using date & hour

DE_Tx

Member
I'm new to Power Query and am struggling learning the syntax.

I need to know the Power Query formula for creating the equivalent of this standard Excel formula:

=AgentId&text(Date,"YYYYMMDD")&hour

Date and Hour are being populated from a field called Period (Date/Time)
Date = int(Period)
Hour = hour(Period)

Custom column would be AgentDateHr = AgentId&Date.ToText(Date,"YYYYMMDD")&hour

Error message says
Expression.Error: We cannot apply operator & to types Text and Number.

Does that mean I need to duplicate Date & Hour columns, convert them to text, and then concatenate them with AgentID.
If so, how do I change the date (currently in m/d/yyyy format) to be "yyyymmdd"?

What would be the simplest way to accomplish all this?
Any help would be appreciated.
 

DE_Tx

Member
A little more persistent digging and I figured this out. I just had to break it down into pieces based on my earlier assumptions.

AgentDateHr = AgentID & Date.ToText([Date],"yyyymmdd")&Number.ToText([Hour],"D")

Hope this helps someone else.
 

DE_Tx

Member
I just found out that Date and Hour are not part of the original data. They had been added in Excel via standard formulas.
So, what would be the DAX equivalent to transform a date/time of 8/23/2016 6:00 PM into a text string of "yyyymmddhh" (e.g. 2016082318)?

The data will only hourly interval data for now.

I know I can transform the date/time into a date and hour by duplicating the column and then changing it, but I prefer to have just one column that converts a date/time into a text string. This text string will be concatenated with the AgentId and this table will be joined to a second table that will need the same key field created.

Thank you for any assistance you can provide.
 

r1c1

Administrator
Staff member
I hope your formula from post #3 solves the Power Query issue. For DAX part, you can use FORMAT formula. Assuming [date] column has the date & time in the format you mentioned, you can use
=FORMAT([date], "yyyymmddhh")
 
Top