Forum Discussion

BSXS's avatar
BSXS
Frequent Visitor
8 years ago
Solved

Merge 2 "date" columns with Add columns from Example OR Merge OR Group By

Im trying to make new column from Year and Month columns.

They have been set to Whole number.

Year is 2015, 2016 etc BUT Month is 1, 2, ,3 .. 10, 11 and 12

The result I want is a new column called Period and result like 201501, 201502... 201511, 201512 etc. Problem is to add a Zero to single digit value

 

None of the 3 above methods work for me. Any suggestions?

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this M code

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Year]&Text.PadStart([Month],2,"0"))
in
    #"Added Custom"

5 Replies

  • BSXS's avatar
    BSXS
    Frequent Visitor
    8 years ago

    I did find one workaround. Make Month into text format. Then you can manually replace 1 with 01 but remeber Adavanced options fill in Matc entire cell content otwerwise 10 will be 010.

    Then highlight Year and Month column, go to Add columns from examples,   choose selection, then  manually type in new column 201601, 201701 and it will automatically fill in the rest. 

     

    Why cant I do this from start? Problem before was it couldnt transform a Month column with 1 only. It needed 01, 02 etc

     

    can you find a simplier way of doing this in Query mode?

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago
    BSXS wrote:

    Im trying to make new column from Year and Month columns.

    They have been set to Whole number.

    Year is 2015, 2016 etc BUT Month is 1, 2, ,3 .. 10, 11 and 12

    The result I want is a new column called Period and result like 201501, 201502... 201511, 201512 etc. Problem is to add a Zero to single digit value

     

    None of the 3 above methods work for me. Any suggestions?

     

    BSXS

    See 

    yearMonth =
CONCATENATE (
    YEAR ( 'calendar'[date] ),
    RIGHT ( CONCATENATE ( 0, MONTH ( 'calendar'[date] ) ), 2 )
)

    or 
    yearMonth= FORMAT('calendar'[date],"yyyyMM")

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Try this M code

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Year]&Text.PadStart([Month],2,"0"))
in
    #"Added Custom"
    • BSXS's avatar
      BSXS
      Frequent Visitor
      8 years ago

      Works. Many Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi BSXS,

         

        Whom are you replying to?  Whose solution worked for you?  Please mark the relevant response as Answer.