Power Query: Using Else If Clauses in Conditional Columns

February 25, 2020 - by Bill Jelen

In my solution to reshaping the data, I wanted a way to see if a column contained an employee name or a value such as Q1, Q2, Q3, Q4. In my solution, I assumed that no one would have a name with 2 characters, and so I added a column to calculate the length of the text in the column.

Jason M avoided the need for the Length column by adding three Else If clauses to his Conditional Column.

The conditional calculation for Employee then looks for Quarter to be Null: if [Quarter] = null then [Category Description] else null.

Here is Jason’s M code:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Quarter", each if [Category Description] = "Q1" then [Category Description] else if [Category Description] = "Q2" then [Category Description] else if [Category Description] = "Q3" then [Category Description] else if [Category Description] = "Q4" then [Category Description] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Employee", each if [Quarter] = null then [Category Description] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Quarter"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Employee"}),
    #"Inserted Distinct Count" = Table.AddColumn(#"Filled Down", "Distinct Count", each List.NonNullCount(List.Distinct({[Category Description], [Employee]})), Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Distinct Count", each [Distinct Count] <> 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Distinct Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Columns1",{"Category Description"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Employee] <> "Dept. Total")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Quarter", "Employee"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Category Description"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Quarter]), "Quarter", "Value"),
    #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Sum",{"Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total"})
in
    #"Reordered Columns"

Ondřej Malinský sent in a solution that used multiple Else If clauses as well:

Matthew Wykle sent in a solution with yet another way to identify the quarters. His method checks both that the text starts with Q and the second digit is less than 5:

if Text.Start([Attribute],1)="Q" and Number.From(Text.Middle([Attribute],1,1))<5 then Text.Start([Attribute],2) else "Total")

Christian Neuberger used this formula to get Employee Name, Filled Down, and then Filtered column 1 to include only Q1, Q2, Q3, or Q4. Oz Du Soleil also used this method.

Excel MVP Ken Puls probably wins with his formula. It looks for an underscore to know if this is not the employee name.

See Ken’s full solution at Excel MVPs Attack the Data Cleansing Problem in Power Query.

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Power Query: Dealing with Multiple Identical Headers.