cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kschubert417 Frequent Visitor
Frequent Visitor

Power Query Nested IF AND Statement

Hi All,

 

I am working with the fiscal calendar below:

January: 01/01/2018 - 01/26/2016

February: 01/27/2018 - 02/23/2018

 

I would like to create a new column in Power Query that identifies the fiscal month a customer wants his/her order. If Customer1 wants his product on January 5, 2018 and Customer2 wants her product on January 29, 2018 I would like the fiscal month to be returned in a new column. Fiscal Month.

 

So I would like the below to happen where columns "Customer" and "Requested Date" are given I want this query to return the column "Fiscal Date:"

Customer | Requested Date | Fiscal Date

Customer1 | 01/05/2018 | 01/01/2018

Customer2 | 01/29/2018 | 02/01/2018

 

I am aware that something similar to Excel and DAX's IF AND statement would need to be used, but I am not very familiar with PowerQuery.

 

Thanks in Advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Power Query Nested IF AND Statement

Hi @kschubert417,

 

Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.

if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26) 
then #date(2018, 1, 1)
else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23)
then #date(2018, 2, 1)
else #date(9999, 12, 31)

Power-Query-Nested-IF-AND-Statement

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
drewlewis15 Member
Member

Re: Power Query Nested IF AND Statement

Do you have a calendar table in your model that represents your fiscal calendar?  As long as that calendar table has a single record for each date, then you could simply merge the calendar table to your customer table and bring in either the fiscal month name (January, February...) or bring in the first date of that fiscal month (you would need that column to exist in your calendar table).

v-jiascu-msft Super Contributor
Super Contributor

Re: Power Query Nested IF AND Statement

Hi @kschubert417,

 

Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.

if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26) 
then #date(2018, 1, 1)
else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23)
then #date(2018, 2, 1)
else #date(9999, 12, 31)

Power-Query-Nested-IF-AND-Statement

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kschubert417 Frequent Visitor
Frequent Visitor

Re: Power Query Nested IF AND Statement

This sounds like a simple option. Do you have any links you can refer me to? I know I can easily create the table.

Highlighted
kschubert417 Frequent Visitor
Frequent Visitor

Re: Power Query Nested IF AND Statement

This works for what I need it for!

 

Turns out there is a difference between a date column and a dattime column so I had to adjust your solution slightly to reflect this. I can see how updating this once a new fiscal calendar comes out may be a pain though.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 207 members 2,046 guests
Please welcome our newest community members: