Power Query Nest If Statement

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,091
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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,091
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,091
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,862
Latest member
lbisacca
Top