Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
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"
Works. Many Thanks
Hi @BSXS,
Whom are you replying to? Whose solution worked for you? Please mark the relevant response as Answer.
@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?
See
yearMonth = CONCATENATE ( YEAR ( 'calendar'[date] ), RIGHT ( CONCATENATE ( 0, MONTH ( 'calendar'[date] ) ), 2 ) )
or
yearMonth= FORMAT('calendar'[date],"yyyyMM")
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |