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...
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"
  • BSXS's avatar
    BSXS
    8 years ago

    Works. Many Thanks

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?