Apr 14 2022 01:10 PM
I am looking for help with this formula in PQ so that I get the Fiscal Quarter, eg Q1, Q2, etc. The formula shows no error but the result is displaying "Table".
paste view:
= Table.AddColumn(#"Change YTD Amt to decimal", "Quarter", each if [Close Date]>= Date.FromText("2021,10,1") and [Close Date]<= Date.FromText("2021,12,31") then "Q1" else Table.AddColumn(#"Change YTD Amt to decimal", "Quarter", each if [Close Date]>= Date.FromText("2022,1,1") and [Close Date]<= Date.FromText("2022,3,31") then "Q2" else Table.AddColumn(#"Change YTD Amt to decimal", "Quarter", each if [Close Date]>= Date.FromText("2022,4,1") and [Close Date]<= Date.FromText("2022,6,30") then "Q3" else "Q4")))
Apr 14 2022 01:32 PM
Assuming I don't make mistake with Notepad the following should fix the issue, not necessarily do what you expect though...
= Table.AddColumn(#"Change YTD Amt to decimal", "Quarter", each
if [Close Date]>= Date.FromText("2021,10,1") and [Close Date]<= Date.FromText("2021,12,31")
then "Q1"
else if Date.FromText("2022,1,1") and [Close Date]<= Date.FromText("2022,3,31")
then "Q2"
else if [Close Date]>= Date.FromText("2022,4,1") and [Close Date]<= Date.FromText("2022,6,30")
then "Q3"
else "Q4"
)
If this does what you expect I would suggest you have a look to #date that's easier and probably more efficient than Date.FromText
Apr 14 2022 01:35 PM
With use each within condition, thus iterate column one more time and generate the table
a = Table.AddColumn(
#"Change YTD Amt to decimal",
"Quarter",
each
if [Close Date]>= Date.FromText("2021,10,1") and
[Close Date]<= Date.FromText("2021,12,31")
then "Q1"
else Table.AddColumn(#"Change YTD Amt to decimal",
"Quarter",
each
if [Close Date]>= Date.FromText("2022,1,1") and
[Close Date]<= Date.FromText("2022,3,31")
then "Q2"
else
Table.AddColumn(#"Change YTD Amt to decimal",
"Quarter",
each
if [Close Date]>= Date.FromText("2022,4,1") and
[Close Date]<= Date.FromText("2022,6,30")
then "Q3"
else "Q4"
)
)
),
This one works:
a = Table.AddColumn(
#"Change YTD Amt to decimal",
"Quarter",
each
if [Close Date]>= Date.FromText("2021,10,1") and
[Close Date]<= Date.FromText("2021,12,31")
then "Q1"
else
if [Close Date]>= Date.FromText("2022,1,1") and
[Close Date]<= Date.FromText("2022,3,31")
then "Q2"
else
if [Close Date]>= Date.FromText("2022,4,1") and
[Close Date]<= Date.FromText("2022,6,30")
then "Q3"
else "Q4"
)
Apr 14 2022 01:47 PM
Alternatively
#"Inserted Quarter" = Table.AddColumn(
#"Change YTD Amt to decimal",
"Quarter",
each
"Q" & Text.From(
if [Close Date] > #date(2022,6,30) or
[Close Date] < #date(2021,10,1)
then 4
else
Number.Mod( Date.QuarterOfYear([Close Date]) + 1, 4)
),
type text)