Results 1 to 5 of 5

Thread: M is for Data Monkey Chapter 24
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Location
    Lafayette, Indiana
    Posts
    560
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default M is for Data Monkey Chapter 24

    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!
    Mike Hirsch
    Using Excel 2016

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,511
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: M is for Data Monkey Chapter 24

    You could use:

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

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Location
    Lafayette, Indiana
    Posts
    560
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: M is for Data Monkey Chapter 24

    To insert the new column, my formula is:
    Code:
    = 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.
    Mike Hirsch
    Using Excel 2016

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Location
    Lafayette, Indiana
    Posts
    560
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: M is for Data Monkey Chapter 24

    I got it. This works:
    Code:
    = Table.AddColumn(#"Inserted Day", "YYYYPP", each Number.ToText([Year]) & Number.ToText([Month],"00"))
    Thanks for the help!
    Mike Hirsch
    Using Excel 2016

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,511
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: M is for Data Monkey Chapter 24

    You could amend that to:

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •