Power query variable

[dicken]

HI,
is it possible to declare a variable in the add custom column dialog of power query so ;
Financial quarter:

if Date.QuarterOfYear([#"Date "]) - 1 = 0 then 4 else
Date.QuarterOfYear[#"Date "]-1 ,

so in dax VAR abc = "Quarter"
RETURN if ( "Quarter"-1 = 0, 4 , "Quarter )

Or somthing similar, I think I have done this at some point but can't remember how, whether I had to use a let statemnt ?

Richard

 

[Kerryx]

Yes in PQ in the editor before the source line simply type

Let
VarableName = 1,

you can then use this variablename in your custom column

 

[Kerryx]

if it is the dates i normally just do as follows in Adv Editor

let
Quarter ={3,3,3,4,4,4,2,2,2,1,1,1},

then in my custom column (Fiscal Qtr) add formula as
=Quarter{Date.Month([Date]-1}

Which i guess is what you may be trying to achieve but entering your "if" statement to check if the above is =0

 

[dicken]

if it is the dates i normally just do as follows in Adv Editor

let
Quarter ={3,3,3,4,4,4,2,2,2,1,1,1},

then in my custom column (Fiscal Qtr) add formula as
=Quarter{Date.Month([Date]-1}

Which i guess is what you may be trying to achieve but entering your "if" statement to check if the above is =0

 

[dicken]

Hello Kerryix ,
Thanks for the help,

Richard.

 

[dicken]

Hi,
Just to update this is what I wanted, if you have a better way please share, I find the use of 'in', also if delcaring multible variables,
so if i have this which works fine,

let QuartF = 
Number.RoundUp(  ( Date.QuarterOfYear([Date] ) / 3 ) ) -1 
in 
if
QuartF = 0 then 4 else QuartF

,


but wanted to break it down;


l

et QuartF = 
Number.RoundUp(  ( Date.QuarterOfYear([Date] ) / 3 ) ) 

let minuseone = QuartF -1 

in  if  minusone -= 0 , 4, miunsone.

, the second let is always an error, I've tried commas , no commas etc , any suggestoins.

Richard.

 

[dicken]

Hi,
Just to update this is what I wanted, if you have a better way please share, I find the use of 'in', also if delcaring multible variables,
so if i have this which works fine,

let QuartF =
Number.RoundUp(  ( Date.QuarterOfYear([Date] ) / 3 ) ) -1
in
if
QuartF = 0 then 4 else QuartF

,


but wanted to break it down;


l

et QuartF =
Number.RoundUp(  ( Date.QuarterOfYear([Date] ) / 3 ) )

let minuseone = QuartF -1

in  if  minusone -= 0 , 4, miunsone.

, the second let is always an error, I've tried commas , no commas etc , any suggestoins.

Richard.

The penny has dropped think I've got how it work, of course the pq 'if' statment doesn't help, all this 'then' or 'else' I keep hitting comma out of habit,

let 
Frac =Number.RoundUp( Date.QuarterOfYear([Date]) / 3),
minusone = Frac - 1, 
anser = if minusone = 0 then 4 else minusone 
in 
 anser