Help with power query formula

[mark84]

Hi eveybody,
I'm trying to transform an excel formula in a power query if statements.
This is the excel formula:

=IF(J2=1;IF(AND(D2=" P";W2="S");ROUND(G2*(1-(N2/10000))-(G2*0,6%);2);IF(AND(D2=" P";W2<>"S");
ROUND($G2*(1-($N2/10000));2);IF($D2=" N";ROUND((G2/(1+(I2/100)))*(1-(N2/10000));2);IF(D2=" D";N2/100))));"")


I've tried to use this in power query:
= if [#" Qty1"]=1 and [#" Dove"]="P" and [Aifa]="S" then Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000))-(([#" PrezPub"]*0.6/100)),2) else if [#" Qty1"]=1 and [#" Dove"]="P" and [Aifa]<>"S" then Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000)),2) else if [#" Qty1"]=1 and [#" Dove"]="N" and [Aifa]<>"S" then Number.Round(([#" PrezPub"]/(1+[#" CodIva"]/100))*(1-([#" Val1"]/10000)),2) else if [#" Qty1"]=1 and [#" Dove"]="D" then [#" PrezPub"]/100 else ""

but I know that it's not possible to use more than one AND condition.

So I tried in this other way but I don't get the same result of the excel formula.

= if [#" Qty1"]=1 then (if [#" Dove"]="P" and [Aifa]="S" then Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000))-(([#" PrezPub"]*0.6/100)),2) else if [#" Dove"]="P" and [Aifa]<>"S" then Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000)),2) else if [#" Dove"]="N" and [Aifa]<>"S" then Number.Round(([#" PrezPub"]/(1+[#" CodIva"]/100))*(1-([#" Val1"]/10000)),2) else if [#" Dove"]="D" then [#" PrezPub"]/100 else "") else ""

I'm going crazy!

Could you help me please?

Thank you
Regards

Marco

 

[JGordon11]

Maybe it's a version issue because your formula works for me. But if it's true that your version is limited to one "and" then you can try the second version:

Your formula:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{" Qty1", type number}, {" PrezPub", type number}, {" CodIva", type number}}),
    Custom = Table.AddColumn(ChangeType, "Custom", each 
        if [#" Qty1"]=1 and [#" Dove"]="P" and [Aifa]="S" then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000))-(([#" PrezPub"]*0.6/100)),2) 
        else if [#" Qty1"]=1 and [#" Dove"]="P" and [Aifa]<>"S" then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000)),2) 
        else if [#" Qty1"]=1 and [#" Dove"]="N" and [Aifa]<>"S" then 
            Number.Round(([#" PrezPub"]/(1+[#" CodIva"]/100))*(1-([#" Val1"]/10000)),2) 
        else if [#" Qty1"]=1 and [#" Dove"]="D" then 
            [#" PrezPub"]/100 
        else "")
in
    Custom

Revised to limit to one "and":

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{" Qty1", type number}, {" PrezPub", type number}, {" CodIva", type number}}),
    Custom = Table.AddColumn(ChangeType, "Custom", each 
        if {[#" Qty1"], [#" Dove"], [Aifa]} = {1,"P","S"} then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000))-(([#" PrezPub"]*0.6/100)),2) 
        else if {[#" Qty1"],[#" Dove"]} = {1,"P"} and [Aifa]<>"S" then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000)),2) 
        else if {[#" Qty1"],[#" Dove"]} = {1,"N"} and [Aifa]<>"S" then 
            Number.Round(([#" PrezPub"]/(1+[#" CodIva"]/100))*(1-([#" Val1"]/10000)),2) 
        else if [#" Qty1"]=1 and [#" Dove"]="D" then 
            [#" PrezPub"]/100 
        else "")
in
    Custom

aFilterR1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1								First query								Second query
2
3	Qty1	Dove	Aifa	PrezPub	Val1	CodIva		Qty1	Dove	Aifa	PrezPub	Val1	CodIva	Custom		Qty1	Dove	Aifa	PrezPub	Val1	CodIva	Custom
4	1	P	S	10	10000	1		1	P	S	10	10000	1	-0.06		1	P	S	10	10000	1	-0.06
5	1	P	T	10	5000	1		1	P	T	10	5000	1	5		1	P	T	10	5000	1	5
6	1	N	T	10	5000	1		1	N	T	10	5000	1	4.95		1	N	T	10	5000	1	4.95
7	1	D	T	10	10000	1		1	D	T	10	10000	1	0.1		1	D	T	10	10000	1	0.1
8	2	D	T	10	10000	1		2	D	T	10	10000	1			2	D	T	10	10000	1
9
Sheet2

 

[mark84]

Thank you very much for your answer.
If I paste the second formula I receive an alert message:
Formula.Firewall: Query 'Pivot best price' (step 'add customized column') refers to other query, so it can't directly access to the data origin. You have to correct your data.

Could you explain me the way to solve the problem? Perhaps because in your formula you wrote "Table 3"?

Thanks

 

[JGordon11]

Yes - you need to change line 1 to retrieve the data from whatever source you are using. The way it is currently written it is retrieving from the active excel spreadsheet from a table called Table3. Replace that line with your first step retrieval code.

 

[mark84]

Sorry but I can't solve the problem.
The source of the data is this file:
C:\Users\marco.restuccia\Desktop\Analisi_competizione\Listini.csv
This file has only one sheet named Listini.
Could you help me with the first part of the formula.

Thank you very much

 

[JGordon11]

maybe

let
    Source = Csv.Document(File.Contents("C:\Users\marco.restuccia\Desktop\Analisi_competizione\Listini.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangeType = Table.TransformColumnTypes(#"Promoted Headers",{{" Qty1", type number}, {" PrezPub", type number}, {" Val1", type number}, {" CodIva", type number}}),
    Custom = Table.AddColumn(ChangeType, "Custom", each 
        if {[#" Qty1"], [#" Dove"], [Aifa]} = {1,"P","S"} then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000))-(([#" PrezPub"]*0.6/100)),2) 
        else if {[#" Qty1"],[#" Dove"]} = {1,"P"} and [Aifa]<>"S" then 
            Number.Round([#" PrezPub"]*(1-([#" Val1"]/10000)),2) 
        else if {[#" Qty1"],[#" Dove"]} = {1,"N"} and [Aifa]<>"S" then 
            Number.Round(([#" PrezPub"]/(1+[#" CodIva"]/100))*(1-([#" Val1"]/10000)),2) 
        else if [#" Qty1"]=1 and [#" Dove"]="D" then 
            [#" PrezPub"]/100 
        else "")
in
    Custom