Mar 08 2022 10:07 AM - edited Mar 08 2022 10:10 AM
Oi,
I want to add a new column to my power query cointaining fixed text + Year + month.
I've tried multiple formula's but keep getting errors,..
Example of the text needed "TEST 202208"
TEST = a fixed text ("TEST")
2022 = Year - Calculated by Formula
08 = Month - Calculated by Formula
I don't have any column containing a date format
Mar 09 2022 01:59 AM
Hi @JustinSane
If Year and Month are Text values:
= Text.Combine({"TEST ", Year, Month})
or
= "TEST " & Year & Month
If Year and Month are Numeric values:
= Text.Combine({"TEST ", Text.From(Year), Text.PadStart(Text.From(Month),2,"0")})
or
= "TEST " & Text.From(Year) & Text.PadStart(Text.From(Month),2,"0")
Mar 10 2022 12:18 AM
Thanks!
Just tried your code and it gives me an error.
"Expression.Error: The name "Year" wasn't recognized. Make sure it's spelled correctly
Mar 10 2022 01:08 AM
Hi @JustinSane
This probably means Year and Month are actually calculated fields as highlighted in the below example
in which case the formula for field TEST is:
= Table.AddColumn(PreviousStepName, "TEST", each "TEST " & Text.From([Year]) & Text.PadStart(Text.From([Month]),2,"0"), type text)
(appropriatly replace PreviousStepName)
If still not good please post at least a picture similar to the above one
Mar 10 2022 12:59 PM
The easiest was is on Power Query editor ribbon
Add Column -> Column From Examples -> All Columns
and add 2-3 samples, correct text where extra leading zero before month appears
Generated formula will be as @L z. suggested, but you don't need to care about syntax and data type.