Instead of Nested IF...THEN, is there a SWITCH equivalent? and, List.Generate issue

[ExcelQuestion]

Hello Experts,

The User would select from the dropdown list within column [Select Frequency]. Then, helper columns [DateAdd Name] and [DateAdd Value] would be populated from the dimension table. The goal is to generate a list of dates based on the selected frequency (ie. monthly, bi-weekly, semi-monthly, quarterly, yearly), shown below.



1) This Function does work except that it's filled with many nested IF...THEN's. Is there an efficient way to rewrite this? Perhaps a SWITCH equivalent in Power Query?

2) All frequencies work...except for the "Semi-monthly 1st, 15th" and the "Semi-monthly 15th, last" options as I am unsure how to reference the Period (the Counter equivalent) in this case.
Is there a way to reference the previous row's date?

The M code for both semi-monthly options would have worked only if the previous date could be referenced. A sample of the desired semi-monthly output is included in the screenshot above.

(Input_Start_Date as date, Periods as number, Select_Frequency as text, DateAdd_Name as text, DateAdd_Value as number) =>

let
Date = Input_Start_Date,

GenerateDates = List.Generate(
()=> [Date = Input_Start_Date, Period = 0],

each [Period] <= Periods,

each [Period = [Period] + 1,

Date =
if DateAdd_Name = "dateaddweeks" then
Date.AddWeeks(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddmonths" then
Date.AddMonths(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddyears" then
Date.AddYears(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddquarters" then
Date.AddQuarters(Input_Start_Date, Period * DateAdd_Value)
else

// Both semi-monthly options (below) doesn't work. Is there a way to reference the previous row's date?

if DateAdd_Name = "date_function" and DateAdd_Value = 1 then

if Date.Day(Input_Start_Date) < 15 then
#date(Date.Year(Input_Start_Date),Date.Month(Input_Start_Date),15)
else Date.StartOfMonth(Date.AddMonths(Input_Start_Date,1))

else

if DateAdd_Name = "date_function" and DateAdd_Value <> 1 then

if Date.Day(Input_Start_Date) < 15 then
#date(Date.Year(Input_Start_Date),Date.Month(Input_Start_Date),15)
else if Input_Start_Date = Date.EndOfMonth(Input_Start_Date) then
#date(Date.Year(Input_Start_Date),Date.Month(Date.AddMonths(Input_Start_Date,1)),15)
else Date.EndOfMonth(Input_Start_Date)

else null

]),

ConvertToTable = Table.FromRecords(GenerateDates)

in
ConvertToTable

The sample file is attached.

Thanks in advance,
Ricky

 

[AliGW]

A couple of possibly useful links:

https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/

https://www.thebiccountant.com/2018...ion-query-editor-in-power-bi-and-power-query/

 

[ExcelQuestion]

Hi AliGW,
I read both links prior to posting this question. I wasn't able to get the SwitchM, M.Switch, nor values = {{result_1, return_value_1},{input, "Undefined"} syntax to work.

Hitting a hurdle because of the IF And...THEN and the semi-monthly List.Generate incorrect date issues. Your help would be greatly appreciated.

Thanks in advance,
Ricky

 

[cyborgski]

Hey Ricky,

Embrace the nested if (as it is the only way that I could think to handle date_function ). FYI, I am unclear on the date_function pattern from your sample data. So the code below handles either the 1st and the 15th of the month or the 15th and last day of the month. And it shows you how to reference the prior date list element.

(Input_Start_Date as date, Periods as number, Select_Frequency as text, DateAdd_Name as text, DateAdd_Value as number) =>


let
Date = Input_Start_Date,


GenerateDates = List.Generate(
()=> [Date = Input_Start_Date, Period = 0],
each [Period] <= Periods,
each [Period = [Period] + 1,
Date = if DateAdd_Name = "dateaddweeks" then
Date.AddWeeks(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddmonths" then
Date.AddMonths(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddyears" then
Date.AddYears(Input_Start_Date, Period * DateAdd_Value)
else
if DateAdd_Name = "dateaddquarters" then
Date.AddQuarters(Input_Start_Date, Period * DateAdd_Value)
else


// Semi-monthly (below) doesn't work without referencing Period. Instead, is there a way to reference the previous row's date as the solution?
// Yes just reference the previous row's date with a bracket
if DateAdd_Name <> "date_function" then null
else if DateAdd_Value = 1 and Date.Day([Date]) = 1
then [Date]+#duration(14,0,0,0)
else if DateAdd_Value = 1 and Date.Day([Date]) = 15
then Date.EndOfMonth([Date]) +#duration(1,0,0,0)
else if DateAdd_Value = 31 and Date.Day([Date]) = 15
then Date.EndOfMonth([Date])
else if DateAdd_Value = 31 and Date.Day([Date]) > 15
then Date.EndOfMonth([Date]) + #duration(15,0,0,0)
else null



]),


ConvertToTable = Table.FromRecords(GenerateDates)


in
ConvertToTable

Good luck,
Mike

 

[cyborgski]

Hi Ricky,

I gave the problem some more thought and thought "who needs a switch function".

Your input table pretty much calls out the Power query function except that you do not have something for Semi-monthly. So, I wrote two functions, Date_AddSemiMonthlyFirst (for the first and 15 days of the month) and Date_AddSemiMonthlyLast (for the 15 and last days of the month). I then modified the input table to name those two functions as well as rewrote the rest of the input tables to look like the standard PQ Date. functions that you were calling in your nested ifs. I modified the input table to use the Expression.Evaluate function to turn the text values into legitimate functions. Now it was a simple matter of rewriting your GenerateDates function to call those functions. Voila, no more nested ifs. No switch either, just bait

And hopefully it is doing what you wanted.

Regards,
Mike

 

[ExcelQuestion]

Thank-you Mike,
Your [Date] guidance helped me a lot!

Further clean up...Monthly, Biweekly, Quarterly, Weekly, Bi-Monthly, and Yearly now only need the [Date] reference.
(Replaced Input_Start_Date and Period)

Here's the full working code, "embracing the nested"! Semi-monthly, regardless of 1st, 2nd, 3rd, through to the 14th does output to be the 15th. Your code offered an insight about Duration. I've incorporated your ideas and now the functions look more consistent.

(Input_Start_Date as date, Periods as number, Select_Frequency as text, DateAdd_Name as text, DateAdd_Value as number) =>

let
Date = Input_Start_Date,

GenerateDates = List.Generate(
()=> [Date = Input_Start_Date, Period = 0],
each [Period] <= Periods,
each [Period = [Period] + 1,
Date = if DateAdd_Name = "dateaddweeks" then
Date.AddWeeks([Date], DateAdd_Value)
else
if DateAdd_Name = "dateaddmonths" then
Date.AddMonths([Date], DateAdd_Value)
else
if DateAdd_Name = "dateaddyears" then
Date.AddYears([Date], DateAdd_Value)
else
if DateAdd_Name = "dateaddquarters" then
Date.AddQuarters([Date], DateAdd_Value)
else

if DateAdd_Name = "date_function" and DateAdd_Value = 1 then
if Date.Day([Date]) < 15 then
// Replaced #date(Date.Year([Date]),Date.Month([Date]),15) with Date.StartOfMonth to be consistent
Date.StartOfMonth([Date]) + #duration(14,0,0,0)
else Date.StartOfMonth(Date.AddMonths([Date],1))
else

if DateAdd_Name = "date_function" and DateAdd_Value <> 1 then
if Date.Day(Input_Start_Date) < 15 then
// Replaced #date(Date.Year([Date]),Date.Month([Date]),15) with Date.StartOfMonth to be consistent
Date.StartOfMonth([Date]) + #duration(14,0,0,0)
else if [Date] = Date.EndOfMonth([Date]) then
// Replaced #date(Date.Year([Date]),Date.Month(Date.AddMonths([Date],1)),15) with Date.EndOfMonth to be consistent
Date.EndOfMonth([Date]) + #duration(15,0,0,0)
else Date.EndOfMonth([Date])
else null

]),

ConvertToTable = Table.FromRecords(GenerateDates)

in
ConvertToTable

9 IF...THEN statements are not the prettiest nor efficient way to achieve the solution, but I see your point. Switch may not be the answer. Thank-you for the next thread post. I will test it and reply back with my findings tonight. So far, the function column using evaluate.expression is very clever! I've never seen it in action before. This will be an eye-opener for me.

Thanks a million, Mike!

Best regards,
Ricky

 

[ExcelQuestion]

Hi Mike,
I didn't answer your comment on my post.

I am unclear on the date_function pattern from your sample data

Originally, I had...
1) IF AND logical test: date_function AND DateAdd Value 1 = semi-monthly due on the 1st and 15th
Followed by another IF logical test: user input start date could be any date from 1st to 14th, THEN always return the 15th
Else: return next month, 1st day

2) date_function AND DateAdd Value 31 = semi-monthly due on the 15th and last day of month (evaluated through IF AND logical test)
Followed by another IF logical test: user input start date could be any date from 1st to 14th, THEN always return the 15th
Else IF logical test: if the previous date is already the end of month, THEN return next month, 15th day
Else: return current month, end of month

The IF...THEN handles any user input date (not limited to only 1st, 15th, or last day) just for ease of use. The semi-monthly output will always be either due on the 1st and 15th, or 15th and last day (payroll, mortgage, maintenance contracts).

Your idea of naming Date_SemiMonthlyFirst and DateSemi_MonthlyLast is better. I now could do without the DateAdd Value entries of 1 or 31. It was not user intuitive anyways. Nobody would have known to enter 31...in fact, any value other than 1 would have still worked for semi-monthly due on the 15th and last day of month...but user's wouldn't have known this. I've cleaned up the GenerateDates IF THEN function (attached). Thank-you!

Now onto your bright idea that one-up's Switch :rockon:

Performance
Increased the sample size so the output goes up to 1.03 million rows (Excel 2013 maximum row limitation is 1.04 million)
Refreshed All: the GenerateDates IF THEN function refreshed about twice as fast as the 3 function method. It's an oddity that nested IF THEN function performed relatively well at 5 seconds vs 10 seconds. Not giving up on this switch alternative because there's a strong potential here.

I wonder if querying 3 functions contributes to the slowdown...what if embedding Date_SemiMonthlyFirst and Date_SemiMonthlyLast inside of the main DateAddGenerate function, would that be faster?

Intuitiveness
Since Date_SemiMonthlyFirst and Date_SemiMonthlyLast no longer require user entry to populate the DateAdd Value, blank would be the logical preference (originally, I used 1 and 31). However, the 3 functions method does require DateAdd Value to be populated with a value (1, in this sample) because of the DateAddGenerate function otherwise blank would result in Error.





Intuitiveness - Allow Blanks
One idea, improve the dimension table...
1) Input DateAdd query, full dataset, Index column
2) filter by Function Name <> null,
DateAddGenerate function handles the straight-forward Date.AddMonths, Date.AddWeeks, etc. functions Date = Function([Date], DateAdd_Value)
3) full dataset, filter by Function Complex Name <> null
triggers Date_SemiMonthlyFirst or Date_SemiMonthlyLast which doesn't need to reference the DateAdd Value
4) append steps #2 and #3, sort by Index



Test Output - what would happen if users enter...
1) Semi-monthly 1st, 15th - enter Jan 2nd instead of 1st (answer is Jan 15 vs result Jan 19)
2) Semi-monthly 1st, 15th - enter Jan 16th instead of 15th (answer is Feb 1st vs result Feb 2)
3) Semi-monthly 15th, last - enter Feb 1st (answer is Feb 15th vs result Mar 15)
4) Semi-monthly 15th, last - enter Feb 2nd (answer is Feb 15th vs result Mar 15)

Perhaps, pasting the semi-monthly formulas from IF...THEN into the semi-monthly functions would work. The benchmark IF...THEN output is included for comparison.



Conclusion
There is a strong potential with building functions as an alternative to switch that handles complex calculations. Well worth exploring, particularly on a large scale, this makes total sense and also easier to troubleshoot rather than digging through nested IF...THEN's.

Sample file is attached. However, I had to delete the large [Input] table dataset, and deleted the Table outputs. The file would have exceeded allow upload file size limit. If you'd like to test the performance as I did, copy the same dataset rows 6 to 19, down to row 23,000. Load both queries to cells G5 and M3.

Thank-you so much, Mike! Your presented options are an eye-opener. I'm learning a lot.

Best regards,
Ricky

Hi Ricky,

I gave the problem some more thought and thought "who needs a switch function".

Your input table pretty much calls out the Power query function except that you do not have something for Semi-monthly. So, I wrote two functions, Date_AddSemiMonthlyFirst (for the first and 15 days of the month) and Date_AddSemiMonthlyLast (for the 15 and last days of the month). I then modified the input table to name those two functions as well as rewrote the rest of the input tables to look like the standard PQ Date. functions that you were calling in your nested ifs. I modified the input table to use the Expression.Evaluate function to turn the text values into legitimate functions. Now it was a simple matter of rewriting your GenerateDates function to call those functions. Voila, no more nested ifs. No switch either, just bait

And hopefully it is doing what you wanted.

Regards,
Mike

 

[cyborgski]

Hi Ricky,

The semi monthly can be re-written to accept blank/null values (see below).

// if months is even then just add half the periods to the current start date
// if months is odd then one less then half the periods to the current start date, then adjust for 1 or 15 of the month


(StartDate as date, optional periods as number) =>


let
Periods = if periods = null then 1 else periods,
GetCloseDate = Date.AddMonths(StartDate, (Periods - ( if Number.IsOdd(Periods) then 1 else 0))/2),
ResultDate = if not Number.IsOdd(Periods) then GetCloseDate
else if Date.Day(GetCloseDate )= 1 then GetCloseDate + #duration(14,0,0,0)
else Date.AddMonths(GetCloseDate,1 ) - #duration(14,0,0,0)


in
ResultDate

I had thought about evaluating and cleaning up the start date. But then I thought, should 1st and 15 actually be 1st date and 14 days later? I think this query does that as-is. Don't know if that is useful, but it would be flexible. We could also have the other function be any start date and last day of the month or last day and 14 days prior. Again, not sure if it would have any use written like that. Your thoughts?


Regarding performance, one reason that the direct call might be so much worse is that PQ is re-running the Expression.Evaluate multiple times (I have found it to be a slow function). Perhaps a table buffer would eliminate the run and solve the issue. I will give some other thought to why the performance might be so bad if this idea does not solve the issue.

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input Start Date", type date}, {"Periods", Int64.Type}, {"Select Frequency", type text}, {"Function Name", type text}, {"DateAdd Value", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"DateAdd Value"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Select Frequency] <> null),
#"Added Function" = Table.AddColumn(#"Filtered Rows", "Function", each Expression.Evaluate([Function Name],#shared & [Date_SemiMonthlyFirst = Date_SemiMonthlyFirst,
Date_SemiMonthlyLast = Date_SemiMonthlyLast ])),
Buffer = Table.Buffer(#"Added Function"),
#"Invoked Custom Function" = Table.AddColumn(Buffer, "Dates", each DateAddGenerate([Input Start Date], [Periods], [Function], [DateAdd Value])),
#"Expanded Dates" = Table.ExpandTableColumn(#"Invoked Custom Function", "Dates", {"Date", "Period"}, {"Date", "Period"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Date", type date}, {"Period", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Function Name", "DateAdd Value", "Function"})
in
#"Removed Columns"


Regards,
Mike

 

[ExcelQuestion]

Hi Mike,

I had thought about evaluating and cleaning up the start date. But then I thought, should 1st and 15 actually be 1st date and 14 days later?

Good question...and I've pondered this overnight. "Frequency" needs to look at an Expense or Liability type variable.

Frequency, liability (Loans, mortgages): Period 0 is the present value (the date of deposit). Period 1 to xx are the payments. If the deposit date is Jan 13th (semi-monthly 1st, 15th frequency), then I should have added a column for the "cutoff input value". Currently, Period 1 would have been Jan 15th. Real world Period 1 should have skipped Jan 15th's due date. Period 1 should be Feb 1st instead...based on the cutoff input value of, for example, 7 days. If start date is within 7 days of Period 1 Jan 15th, then it's next due date. This extra logic only applies for Period 1. All subsequent periods are the standard 1st, 15th, or last day.

Frequency, expense (payroll, maintenance): There are no Period 0. The start date is the occurrence of the expense. Jan 13th within the cutoff input value of, for example 7 days, would cover the expense until Feb 1st (semi-monthly 1st, 15th frequency.
But Jan 13th's expense is only partial + second half Jan, you ask?!
Yes! I would have added a Period 1 pro-rated column. Period 2 is Feb 1st.

We could also have the other function be any start date and last day of the month or last day and 14 days prior. Again, not sure if it would have any use written like that. Your thoughts?

Semi-monthly should always be set to either 1st and 15th, or 15th and last day. The function has to work around start date, regardless of 1st, 2nd, 3rd day etc. I think the above comment would resolve this variable. I will create the solution building upon the codes you've provided already. Thank-you!

Now back to the codes (Switch alternative)...

Table.Buffer made little difference. With output of 1 million rows, I've averaged 9.5 seconds vs 10 seconds refresh. The Input IF...THEN was still faster. This speed difference is perplexing for sure.

Periods = if periods = null then 1 else periods

Nice! The Date.Add Value column now works with null.

I like the functions method. How would you combine Semi_MonthlyFirst and Semi_MonthlyLast into the DateAddGenerate function? It'll be a cleaner Queries Workbook.

Thanks for the raising good questions. It definitely improves the user experience.

Best regards, Mike.

Ricky

 

[cyborgski]

Hi Ricky,

I did not have time to change the Date_AddSemi functions based on your feedback. Instead, this workbook just shows how you might call them using the nested if from within Date_Generate.

Regards,
Mike

 

[ExcelQuestion]

Thank-you Mike,
This is great! I've learned a lot from you.

No worries, I will modify the Date_AddSemi functions. This latest and previous files have all provided excellent guidance. I'm very happy.

Many thanks, Mike...coming to my rescue again!

Best regards,
Ricky

Hi Ricky,

I did not have time to change the Date_AddSemi functions based on your feedback. Instead, this workbook just shows how you might call them using the nested if from within Date_Generate.

Regards,
Mike