I only tried to illustrate what @Riny_van_Eekelen suggested and didn't test the formula. As for O5 - question is bit abstract, not clear in which part of formula you'd like to add it and what formula shall do with it.
Perhaps you may illustrate the logic if not with draft formula, when by diagram like
(again didn't test)
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
RemoveListsPerhaps 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
Accepted Solutions