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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdrammeh
Helper III
Helper III

How to add a blank cell when formula returns error or null in Power Query

Hello,

 

I have two calculated columns with different expressions in Power Query. I want to calculate the difference between the two columns and return a blank value where the formula returns an error message. 

 

The first column (% Change) is calculated by the difference of =  (Projected $ - Cost)/Projected

The second column used the result of the first column and subtracted it from 1. i.e. % Complete = 1-(% Change) value. 

 

This way when the result is displayed on my power PowerPivot table, the value in the cell remains blank instead of "NA#".Also, if there is a better way to do this calculation, please share your thoughts as well.Exhibit 1.PNGExhibit -2.PNG

 

Thanks!

1 ACCEPTED SOLUTION

Yes, I do suggest you use DAX measures in Power BI Desktop  (or Excel if you are using Excel with powerpivot).

 

View solution in original post

3 REPLIES 3

Hi @mdrammeh

 

I think you should not use Power Query but Dax language to perform this kind of calculations.

And because it involves ratios, you should create DAX measures rather than calculated columns - otherwise you'll get wrong figures when analysing your percentages.

Why ? Because a/b + c/d + e/ f is NOT equal to (a + c + e) / (b+d+f)

 

You should always do "the ratios of the sum rather than the sum of the ratios" (Kimball).

 

So create these 2 measures:

[Pct Change] = 1 - Sum(Table[Cost]) / Sum (Table[Projected])

[Pct Complete] = 1 - [Pct Change]

 

Format them as % and use them in any graphs. These measures will automaticcaly adjust to filter context.

Your Pct calculations will be both correct and dynamic.

So are you suggesting I use DAX functions in Excel or Power BI instead of Power Query? Could you share an example from the Query editor? 

Yes, I do suggest you use DAX measures in Power BI Desktop  (or Excel if you are using Excel with powerpivot).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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