Power Query Nest If Statement

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,145
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I need to create a nested if statement as a custom column using the Month column (3 letter abbreviation) to create a an integer for each month like below. How would I create this nested if statement?

Jan = 1
Feb= 2
Mar = 3
May = 4
Jun = 5
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Using this data in an Excel Table named Table1:
Code:
MthName
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

This query, named MthXRef, creates a Mth/Value XRef table:
Code:
Table.FromRecords({  
[Mth="Jan", MthVal=1], 
[Mth="Feb", MthVal=2],
[Mth="Mar", MthVal=3],
[Mth="May", MthVal=4],
[Mth="Jun", MthVal=5]})
This query connects to Table1 and merges it
with the MthXRef query to return the values:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"MthName"},MthXRef,{"Mth"},"MthXRef",JoinKind.LeftOuter),
    #"Expanded MthXRef" = Table.ExpandTableColumn(#"Merged Queries", "MthXRef", {"MthVal"}, {"MthVal"})
in
    #"Expanded MthXRef"

These are the query results:
Code:
MthName   MthVal
Jan       1
Feb       2
Mar       3
Apr       null
May       4
Jun       5
Jul       null
Aug       null
Sep       null
Oct       null
Nov       null
Dec       null
Is that something you can work with?
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,145
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Using this data in an Excel Table named Table1:
Code:
MthName
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

This query, named MthXRef, creates a Mth/Value XRef table:
Code:
Table.FromRecords({  
[Mth="Jan", MthVal=1], 
[Mth="Feb", MthVal=2],
[Mth="Mar", MthVal=3],
[Mth="May", MthVal=4],
[Mth="Jun", MthVal=5]})
This query connects to Table1 and merges it
with the MthXRef query to return the values:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"MthName"},MthXRef,{"Mth"},"MthXRef",JoinKind.LeftOuter),
    #"Expanded MthXRef" = Table.ExpandTableColumn(#"Merged Queries", "MthXRef", {"MthVal"}, {"MthVal"})
in
    #"Expanded MthXRef"

These are the query results:
Code:
MthName   MthVal
Jan       1
Feb       2
Mar       3
Apr       null
May       4
Jun       5
Jul       null
Aug       null
Sep       null
Oct       null
Nov       null
Dec       null
Is that something you can work with?

Thanks for taking to time to look into this but I was just looking to a create a Custom Column in Power Query because I already have other columns in my Excel and wasn't trying to create a separate table. I'm pretty sure a simple IF statement would do it but I'm not sure how the syntax works in Power Query.
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,145
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Was able to figure using Conditional Column button in Power Query, it's a intuitive user interface for creating the nested IF statement I was looking for. Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,453
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top