Forum Discussion

Copper Contributor

Mar 08, 2022

Power Query Date

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" TE...

BI & Data Analysis

Silver Contributor

Mar 09, 2022

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

JustinSane
Copper Contributor
Mar 10, 2022
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

Lorenzo
- SergeiBaklan
  Diamond Contributor
  Mar 10, 2022
  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
  
  Generated formula will be as Lorenzo suggested, but you don't need to care about syntax and data type.
- Lorenzo
  Silver Contributor
  Mar 10, 2022
  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

Resources