Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
IfaZ_1
Helper I
Helper I

How to merge queries and if formula

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 

 

IfaZ_1_0-1691734410571.png

Thank youu

 

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

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

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"]

View solution in original post

wdx223_Daniel_0-1691744441120.png

wdx223_Daniel_1-1691744527951.png

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

 

 

View solution in original post

9 REPLIES 9
wdx223_Daniel
Super User
Super User

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"]

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

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.

ohhhh, thank you. I understand now. 

 

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

 

IfaZ_1_0-1691738957817.png

 

wdx223_Daniel_0-1691739795853.png

 

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

wdx223_Daniel_0-1691744441120.png

wdx223_Daniel_1-1691744527951.png

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

 

 

IfaZ_1_1-1691746165867.png

I didnt get 8670. 

IfaZ_1_2-1691746371493.png

 

may those duplicated rows come from other previous steps

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.