How to write a Multiple (nested) IF in Power Query

⏬ Click to check out the description for info and links⏬
I show 2 methods to write a multiple IF formula in Power Query including using an "Approximate Match" method
00:00 Intro
00:34 The Setup and Goal
01:24 A better way of writing a nested IF
06:54 How to do an Approximate Match in Power Query
Download the file
aasolutions.sharepoint.com/:f...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
accessanalytic.com.au/

[@chrism9037]

Awesome video, thanks Wyn

[@AccessAnalytic]

Cheers Chris

[@ennykraft]

If the start time of a person is the same as the start time of each shift, you might get the wrong results. That happens when the person's name is on top the null value which the second table creates (no names in that one). You shouldn't do just one sort. Immediately after the first one, add another sort. In this case sort the names. Since null is sorted on top, you now can fill down without any errors. I used to solve this with a combine but I like your method.

[@AccessAnalytic]

Thanks for the suggestion

[@justrelaxmusic5131]

Awesome Wyn. Love your videos. Thanks for the wonderful stuff.

[@AccessAnalytic]

Thank you Mumtaz!

[@mirrrvelll5164]

I was today years old with that IF method from you...never knew that LET-IN can be used as Custom Column :O

[@AccessAnalytic]

It’s rarely used but can be useful 😀

[@ianrhile]

I love the second method! Thanks, Wyn.

[@AccessAnalytic]

Cheers Ian

[@tracylippiatt3238]

I love the Fill Down - what a simple solution

[@AccessAnalytic]

Yeah it’s pretty cool

[@viktorasgolubevas2386]

Thanks for sharing!
ugly but minute workaround if we leave [Time Started] as Decimal and use Conditional Column for if-else skeleton :)
let
Source = Excel.CurrentWorkbook(){[Name="tblShifts"]}[Content],
#"Added Conditional Column" = Table.AddColumn(Source, "Shift", each
if [Time Started]*24 < 6 then "Night"
else if [Time Started]*24 < 15 then "Morning"
else if [Time Started]*24 < 23 then "Afternoon"
else "Night")
in
#"Added Conditional Column"

[@AccessAnalytic]

Yep, that's another approach

[@Donkeys_Dad_Adam]

Excellent as always Sir!

[@AccessAnalytic]

Cheers 😀

[@jackleong5212]

WOW! Thanks for sharing!
This is really goood when dealing with multiple segmentations, sub-segmentations, and categorization😍
Nested IF might become really complicated when if the source was empty and so on...
With tables like the 2nd one is much easier to understand and benefit for both creator and user

[@AccessAnalytic]

You’re welcome

[@Bhavik_Khatri]

Thank you for the great video

[@AccessAnalytic]

You're welcome

[@sanchowitfurrows1561]

Wyn, you literally saved me hours of waiting time!
My initial approach was
1) to have Shifts table with "Start Shift" and "End Shift" columns,
2) then Merge Shifts table to the Timetable,
3) then expand Shifts with replicating timetable rows,
4) adding calculated column to check that Timetable[Start Time] fits between Shifts[Start Time] and Shifts[End Time]
5) filtering out false rows
You can imagine computational complexity when I have 800K rows in Timetable and 12 rows in Shifts!
I'm feeling shivers of excitement to try it tomorrow!

[@AccessAnalytic]

For big sets of data like yours, the most efficient approach might be to round the start time to the nearest hour / half hour / whatever your shift pattern needs, and then merge with a table listing 30 minute intervals in the day and what shift they belong to

[@jppinto4926]

Thanks for the videos. Very helpful. Being pedantic though, in aussie slang:
avo = avocado;
arvo = afternoon.

[@AccessAnalytic]

Good pick up on the typo

[@dharmendarrana4191]

Great Sir

[@AccessAnalytic]

Thanks

[@FRANKWHITE1996]

Awesome ❤

[@AccessAnalytic]

Cheers 😁

[@chrisberardi2304]

What you missed: If you do what you did, then go back to the Advanced Editor, you have quite a bit of extra code. It would have been nice to look at that. I ended up with two "in" and two "let"

[@AccessAnalytic]

I didn’t think of that as being something that would surprise people who are used to going into the advanced editor. But thanks for the feedback.

[@Andy-fd5fg]

oooo parameters and sub-routines.... more of them please

[@AccessAnalytic]

😀

[@chiragdabhi4929]

Good.

[@AccessAnalytic]

Thanks

[@alinelung]

Great information!! Thanks !!
How can i change formula if during the weekend i work in 2 shifts and in Workday i have 3 shifts?
Thank you!!!

[@AccessAnalytic]

I think that’s a more in depth question about how your data is structured. I’d suggest posting your question and an example to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
Or
community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
Or www.reddit.com/r/excel/?

[@SuperJorgeRegula]

Thanks. What is the benefit of doing it in PowerQuery as opposed adding a calculated column with DAX using switch true?

[@AccessAnalytic]

Great question. Generally it’s considered best practice to add your columns “ as far upstream as possible”. So ideally add it to your source database, then next best is Power Query then DAX.
Also if you just want the results in a Table in Excel then you can’t use DAX

[@abdulsaidmkamara7305]

I love the append method , writing M language still scares me 😅😅😅

[@AccessAnalytic]

It’s practice and a never ending learning journey

[@nevenmesic2856]

Can i for example say if something in column is null then return value from different column else give me value from original column? Tried to combine columns but values are numbers not text...

[@AccessAnalytic]

Yep, you can create a conditional column ( which is a wizard to create an IF statement ) . There’s a button on the Add Column tab

[@mattmatt245]

That'll break query folding. I still need to write sql :(

[@AccessAnalytic]

Ah yeah, that’s another consideration to throw in the mix. I forget as I rarely deal with SQL

[@HandyPaladin]

Less a question about this video than a suggestion for another one.
One thing I find frustrating when using Power Query is it refreshing the data each time I add a new step.
For example, I have a query that appends all of the service snapshots in a folder, extracts one column (Service Id), removes duplicates, sorts accending, and outputs to a table.
I want to add some cleaning steps because some of the Ids have suffixes that I don't need, but every time I add a step the query seems to import the base data again.
Is there any way to change that behaviour?

[@AccessAnalytic]

The main one to switch off is under settings - data load - disable background preview refresh ( or something along those lines ) as that prevents refresh of preview other queries when editing your query.
Another technique is to load raw data to a dataflow and then reference that with the clean up steps

[@HandyPaladin]

@@AccessAnalytic thanks. I will look for that and give it a try.

[@chiragdabhi4929]

I want simple if formula for coloum where value less than 100. I want if formula for that value..if

[@josh_excel]

if [Column1] < 98 then 1 else if [Column1] < 95 then 2 else 3

[@AccessAnalytic]

Use the conditional column button.
Start with the lowest number
if “column” < 95 then 2 else
If column < 98 then 1
Else
3

[@AccessAnalytic]

Don’t forget that the order matters, lowest first if doing less than otherwise the first criteria only will be met

[@chiragdabhi4929]

@Access Analytic got it ..thanks for reply

[@chiragdabhi4929]

Can we compare two column data ??

[@Dan_De_Man]

Wouldnt let me enter let in custom column. I enterd let then enter and it changes to = Lines ToText

[@AccessAnalytic]

Press space bar after typing let

[@Dan_De_Man]

Thanks. One other thing If wanting to write in conditional you said start with lowest but how do you write with times < 6:00 night else < 1500 morning else afternoon. Doesn't work as 2300 is Afternoon as less than 0600 only goes back to 0000
Maybe >= 2300 night
Else if < 06:00 night
Else if < 15:00 morning
Else
Afternoon

[@AccessAnalytic]

That sounds like the example i address in my video?

[@Dan_De_Man]

@@AccessAnalytic it is, but was wondering if that's the best way to write it. Initially I tried starting with lowest value but couldn't get it to work properly so tried to follow example in your let argument

[@AccessAnalytic]

That was the simplest approach I could think of

[@sanchowitfurrows1561]

Let's imagine Karen's "Time Started"=3:00PM and Afternoon Shift "Time Started" = 3:00PM.
What can ensure us that Sorting will put Shifts row above Timesheet row?

[@AccessAnalytic]

Immediately after sorting by time maybe sort by the shift column. Also add an index column after the sort and before the fill down to ensure the sort is “locked” as occasionally Power Query doesn’t respect the sort order.

[@therealbmaphill]

It's Arvo not Avo... Avo would be avocado or my ex got a restraining order on me called an AVO ;)

[@AccessAnalytic]

😊 yeah my bad, you’re not the first to pull me up on that one

[@therealbmaphill]

@@AccessAnalytic Its ok I laughed. Thanks for the nice video :)

[@josh_excel]

Here is a Function that simulates VLOOKUP where we can put "true" for approximate matching:
Name this function VLOOKUP:
(lookupValue as any, Range as table, colIndex as number, approximateMatch as logical) =>
let
// Sort table by first column and buffer
sortedRecords = Table.Buffer(Table.Sort(Range,{{Table.ColumnNames(Range){0}, Order.Ascending}})),

L1 = Table.ToColumns(sortedRecords){0},
L2 = Table.ToColumns(sortedRecords){colIndex -1},
match = if approximateMatch = true then

List.Accumulate(
{0..List.Count(L1)-1},
L1{0},
(Tstart,index)=> if lookupValue >= L1{index} then L2{index} else Tstart)

else
List.Accumulate(
{0..List.Count(L1)-1},
"#N/A",
(Tstart,index)=> if lookupValue = L1{index} then L2{index} else Tstart)
in
match

[@AccessAnalytic]

Thanks