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

[BSXS]

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?

[Ashish_Mathur · Accepted solution]

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"

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

Works. Many Thanks

[Ashish_Mathur]

Hi @BSXS,

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

[Eric_Zhang]

---

@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")

[BSXS]

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?