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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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