M is for Data Monkey Chapter 24

[MikeDBMan]

I am creating a dynamic calendar table as discussed in M is for Data Monkey Chapter 24. I have the following fields, among others: Year and Month, both Whole Numbers. I wish to create a concatenated field, YYYYPP, which is the concatenation similar to this: [year] & right("0" & [month],2), so 2019, May (05) would be 201905 and 2019, December (12) would be 201912. I cannot get the formula to work in Power Query. Please help!

 

[RoryA]

You could use:

Number.ToText([Year]) & Number.ToText([Month],"00")

 

[MikeDBMan]

To insert the new column, my formula is:

= Table.AddColumn(#"Inserted Day", "YYYYPP", (each Date.Year([Date])), type number)

and I get just the year, 2019, as the result. Since this is a dynamic table, I am not sure I know how to build your formula into the line above. I've been trying for a few hours and nothing works.

 

[MikeDBMan]

I got it. This works:

= Table.AddColumn(#"Inserted Day", "YYYYPP", each Number.ToText([Year]) & Number.ToText([Month],"00"))

Thanks for the help!

 

[RoryA]

You could amend that to:

= Table.AddColumn(#"Inserted Day", "YYYYPP", each Date.ToText([Date], "yyyyMM"))