Power Query Date

Copper Contributor

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

4 Replies

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

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

 

@L z. 

Hi @JustinSane 

 

This probably means Year and Month are actually calculated fields as highlighted in the below example

 

Screenshot.png

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

@JustinSane 

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

image.png

Generated formula will be as @L z.  suggested, but you don't need to care about syntax and data type.