Power Query Multiple IF Conditions in Custom Column

Alicia_Bucci 

Convert logic (if I understood it correctly) to Power Query that's with some coding.

First way with minimum one

- query the table and add Index, nothing more

- reference this one, remove all columns but Index and all AST.. Select Index and Unpivot Other columns

- add another column replacing all values where Text.StartsWith "BLANK" replace on null and remove Value column

- Group By column Index without aggregation, manually add selection only of Custom column in result and keeping distinct values

- add another column removing rows with null from above, remove Data column

- add column with number of rows in each table

- add one more columns with text in first row of each table and remove column with tables

- add column with conditional result 

and remove other but Index and Result columns

Now merge first table with above one on Index into new query, expand Result.

If you add more columns the only you need is to change columns selected at the beginning of second query.

Another variant is do everything with lists, more coding, perhaps bit more flexible and less steps. Not sure that's better, Power Query is optimized for tables, not lists. And here is sample how to automate columns selection assuming some logic in their names.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(
        Source, [PromoteAllScalars=true]),
    prevStep = #"Promoted Headers",

    GetValues = Table.AddColumn(
        prevStep,
        "values",
        each
            Record.ToList(
            Record.SelectFields(
                _,
                List.Select(
                    Table.ColumnNames( prevStep ),
                    each Text.StartsWith(_, "AST")
                )
            )
    )),
    CleanLists = Table.AddColumn(
        GetValues,
        "notBlanks",
        each List.Distinct(
                List.RemoveNulls(
                    List.Transform(
                        [values],
                        each
                            if Text.StartsWith( _, "BLANK")
                            then null
                            else _ )
                )
        ) ),
    RemoveValues = Table.RemoveColumns(CleanLists,{"values"}),
    ApplyCondition = Table.AddColumn(
        RemoveValues,
        "Result",
        each
            if List.IsEmpty( [notBlanks] )
            then "Investigate"
            else
                if List.Count( [notBlanks] ) = 1
                then List.Single( [notBlanks] )
                else "House"
    ),
    RemoveLists = Table.RemoveColumns(
        ApplyCondition,
        {"notBlanks"}
    )
in
    RemoveLists

Perhaps other variant exist, but in any case for flexible number of columns on which make the selection that will be bit more complex than nested if ... then ... else