How to merge queries and if formula

[IfaZ_1]

Hi, 

I have few questions that I need help. I'm trying to automate some of the formula from excel to power query. 

Latest Data : 8670 data point 

Past Data : 8794 data point

Q1- I have merged Past Data into Latest Data using left join. AccountID|Subheder|AssessementDate (Latest Data) left join AccountID|Subheader|AssessmentDate (Past Data). 

However, when I merged the above columns I got 15,518 data point. I believe it is due to duplication. I'm wondering is there any way I can get the same data point as Latest Data which is 8670? when I remove duplication it become less than this. 

Q2 - Based on the merged of Latest Data and Past Data, I wanted to create a column with this formula from excel (IF(OR(Q2=0,Q2="N/C"),IF(AND(OR(F2="HIGH",F2="MODERATE"),H2="Accepted"),"Accepted with moderate",""),Q2)

which translated into this formula in power query. However, I'm getting an error and honestly I'm not sure what went wrong 

Thank youu

Here is my sample file: https://we.tl/t-yuW7BHDEnw 

[wdx223_Daniel · Accepted solution]

Q1

you can insert a step before expand the nested table column to extract only one row from that table.

=Table.TransformColumns(#"Merged Queries",{"Past_Data",each Table.FirstN(_,1)})

Q2

=if List.Contains({0,"N/C"},[#"Designation with N/C"]) and List.Contains({"HIGH","MODERATE"},[Violation Severity Level]) and [Designation Index]="Accepted"
then "Accepted with moderate"
else [#"Designation with N/C"]

[wdx223_Daniel · Accepted solution]

Q1

you can insert a step before expand the nested table column to extract only one row from that table.

=Table.TransformColumns(#"Merged Queries",{"Past_Data",each Table.FirstN(_,1)})

Q2

=if List.Contains({0,"N/C"},[#"Designation with N/C"]) and List.Contains({"HIGH","MODERATE"},[Violation Severity Level]) and [Designation Index]="Accepted"
then "Accepted with moderate"
else [#"Designation with N/C"]

[IfaZ_1]

Hi, 

It works! Thank youuu so much.

Do mind explain what is the difference between my formula and yours so I can understand it better

[wdx223_Daniel]

you can revise your formula as this

if ([#"Designation with N/C"]=0 or [#"Designation with N/C"]="N/C") and ([Violation Severity Level]="HIGH" or [Violation Severity Level]="MODERATE"}) and [Designation Index]="Accepted"
then "Accepted with moderate"
else [#"Designation with N/C"]

[#"Designation with N/C"]=0 or "N/C" is not a valid M code.

[IfaZ_1]

ohhhh, thank you. I understand now. 

for Q1- what should I input at the front there where I highlighted? 

[wdx223_Daniel]

[IfaZ_1]

It did not change anything. still with duplication data point which is 15,518 instead 8670

[wdx223_Daniel · Accepted solution]

i can get 8670 rows, but it is a performance disaster. it took 10 mins to load

[IfaZ_1]

I didnt get 8670. 

[wdx223_Daniel]

may those duplicated rows come from other previous steps