Dec 06 2021 06:36 AM
Good morning,
I have a formula calculated in Excel that I am now looking to calculate in Power Query. I know I can only use column reference while writing IF statements in PQ. Can you please let me know what the Custom Column would be based on the below Excel calculation?
=IF(M5=N5,M5,IF(AND(M5="BLANK1",N5="BLANK2"),"Investigate",IF(AND(M5<>"BLANK1",N5="BLANK2"),M5,IF(AND(M5<>"BLANK1",N5<>"BLANK2",M5<>N5),"300-Corporate",IF(AND(M5="BLANK1",N5<>"BLANK2"),N5,"ERROR")))))
Thank you in advance!
Dec 06 2021 07:16 AM
@Alicia_Bucci In PQ the manner of writing IF formulae is quite different from Excel's IF function. It would look something like this:
= try
if something then this else that
otherwise "error message"
and you can nest these if / then / else statements. How exactly is difficult to say without seeing your file.
Dec 06 2021 07:25 AM
@Riny_van_Eekelen Thank you for the reply.
I am struggling a bit with the difference from Excel IF function to PQ. Attached is a sample file I am working on. I was originally trying to work with 3 columns to create an IF formula but could not get it to work the way I needed it to, so I am now going to work with 2 columns, which is the original Excel formula I posted.
Dec 06 2021 07:40 AM
@Alicia_Bucci Thanks, but can't work on this file. It links to a file on your local system.
Dec 06 2021 07:50 AM
SolutionI'd simplify initial formula first, perhaps
=IF( M5=N5,
M5,
IF( M5="BLANK1",
IF( N5="BLANK2","Investigate", N5),
IF( N5="BLANK2",
M5,
IF( M5 <> N5,
"300-Corporate",
"ERROR" ) ) ) )
with that
=if [M] = [N]
then [M]
else
if [M] = "BLANK1"
then
if [N] = "BLANK2" then "Investigate" else [N]
else
if [N] ="BLANK2"
then [M]
else
if [M] <> [N]
then "300-Corporate"
else "ERROR"
Dec 06 2021 09:04 AM
@Sergei Baklan Thank you for your help! It is greatly appreciated.
If I wanted to add "O5" to this formula, is that doable or will that create an issue with the formula?
Dec 06 2021 12:03 PM
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)
Dec 07 2021 10:30 AM
Here is the Excel IF statement, which gets more complex when adding AST3. My issue that I am running into is when I need to add AST4, AST5, and possibly AST6. Is there a better way to calculate this formula with adding the additional AST from 1-6? Would this be easier to formulate in Power BI?
IF(AND(A2="BLANK1",B2="BLANK2",C2="BLANK3"),"Investigate",IF(AND(A2=B2,A2=C2),A2,IF(AND(A2<>"BLANK1",B2="BLANK2",C2="BLANK3"),A2,IF(AND(A2=C2,B2="BLANK2"),A2,IF(AND(A2<>C2,A2<>"BLANK1",C2<>"BLANK3",B2="BLANK2"),"House",IF(AND(A2<>"BLANK1",B2<>"BLANK2",A2<>B2,C2="BLANK3"),"House",IF(AND(A2="BLANK1",B2<>"BLANK2",C2="BLANK3"),B2,IF(AND(A2="BLANK1",B2<>"BLANK2",B2<>C2),"House",IF(AND(A2="BLANK1",B2="BLANK2",C2<>"BLANK3"),C2,IF(AND(A2<>"BLANK1",B2<>"BLANK2",C2<>"BLANK3",A2<>B2,B2<>C2),"House",IF(AND(A2="BLANK1",B2=C2),B2,"ERROR")))))))))))
Here is the illustration of how this is laid out in Excel. I included a copy of the file as well.
AST1 | AST2 | AST3 | Expected Result | Line Item Formula | Comp Territory |
BLANK1 | BLANK2 | BLANK3 | Investigate | Investigate | Investigate |
T1 | T1 | T1 | T1 | T1 | T1 |
T1 | BLANK2 | BLANK3 | T1 | T1 | T1 |
T1 | BLANK2 | T1 | T1 | T1 | T1 |
T1 | BLANK2 | T2 | House | House | House |
T1 | T2 | BLANK3 | House | House | House |
BLANK1 | T2 | BLANK3 | T2 | T2 | T2 |
BLANK1 | T2 | T1 | House | House | House |
BLANK1 | BLANK2 | T2 | T2 | T2 | T2 |
T1 | T2 | T3 | House | House | House |
BLANK1 | T2 | T2 | T2 | T2 | T2 |
Dec 07 2021 01:17 PM
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
Dec 06 2021 07:50 AM
SolutionI'd simplify initial formula first, perhaps
=IF( M5=N5,
M5,
IF( M5="BLANK1",
IF( N5="BLANK2","Investigate", N5),
IF( N5="BLANK2",
M5,
IF( M5 <> N5,
"300-Corporate",
"ERROR" ) ) ) )
with that
=if [M] = [N]
then [M]
else
if [M] = "BLANK1"
then
if [N] = "BLANK2" then "Investigate" else [N]
else
if [N] ="BLANK2"
then [M]
else
if [M] <> [N]
then "300-Corporate"
else "ERROR"