Power Query Nest If Statement

[legalhustler]

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

 

[Ron Coderre]

Using this data in an Excel Table named Table1:

MthName
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

This query, named MthXRef, creates a Mth/Value XRef table:

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:

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:

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]

Using this data in an Excel Table named Table1:

MthName
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

This query, named MthXRef, creates a Mth/Value XRef table:

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:

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:

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]

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!

 

[sandy666]

maybe read:

https://support.office.com/en-us/ar...38c-9d29-c6d29cb8df6a?ui=en-US&rs=en-US&ad=US
https://support.office.com/en-us/ar...04f-a9fe-1a04d52f249b&ui=en-US&rs=en-US&ad=US