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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dbooksta
New Member

Including Excel formulas in custom columns

I am using Power Query to get data from a CSV into an Excel table.  I want to insert various columns that have Excel formulas in them.  If I do this in the Excel table then when I refresh the data source it corrupts the columns and the ordering I created.  If I try to enter the Excel formulas in the Power Query editor as a Custom Column then I get Power Query Expression.Errors.  Here's a simple example that covers my scenario:

 

From a CSV I get three columns:

 

NameGroupWeight
AppleFruit.5
BananaFruit.6
CarrotVeggie.7

 

Now I return those to an Excel worksheet and I insert a column named "Rank" before column "Group" and enter the following formula in B2: =FLOOR.MATH(10*(COUNTIFS(C:C,C2,D:D,">"&D2)),2)  ... and Excel does what I expect to fill that formula down that column.

 

Then I insert another column named "Price" before column "Weight" containing a function "LastPrice" that is defined in an Excel Add-In.  Populating the following formula in this new column's cell D2 works perfectly: =LastPrice(A2) ... or equivalently =LastPrice([@Name])

 

Constructing the table in Excel this way I end up with what I want: 

 

NameRankGroupLastPriceWeight
Apple10Fruit30.5
Banana0Fruit40.6
Carrot0Veggie50.7

 

But if I refresh the data source then I am left with a mess.

 

So I thought maybe I need to define everything in Power Query.  But if I try to put a Custom Column that uses "LastPrice()" there I get an Expression.Error ... and it doesn't look like my CountIfs will be handled any more gracefully.

 

So what is the right way to construct this table in Excel using Power Query (or not)?

1 ACCEPTED SOLUTION

Hi @dbooksta ,

 

When you import data in PQ, PQ purely loads whatever data you have. One thing PQ doesn't like is #N/A, #REF!, etc. I am referring to source data. Once data is imported, you can load the data into excel, and then apply the add-in function - LastPrice(), but don't add it between columns in the loaded table. Instead, you can add columns at the end of the table and apply the add-in function.

 

Please note that LastPrice() is an add-in for excel (I assumed it is a VBA custom function) not for PQ, so the solution is to replicate the formula in PQ, providing we know the logic of LastPrice(), then we should be able to replicate it.

 

I hope the explanation is clear.

 

 

Regards

KT

View solution in original post

4 REPLIES 4
Snarglefarg
New Member

Try this:

=FLOOR.MATH(10*(COUNTIFS([Group],[@Group],[LastPrice],">"&[@LastPrice])),2)

KT_Bsmart2gethe
Impactful Individual
Impactful Individual

HI @dbooksta ,

 

If you loaded the csv file to excel through PQ, don't insert column(s) between columns on the loaded table. 

 

It'd be great if you could provide the logic of how to get rank and last price as I can only assume the provided formula (i.e. column A - Name? .. ) and I am not certain.

 

Regards

KT 

The "LastPrice()" function I am using in this case is provided only through an Excel Add-In.  So is there a way to get PQ to ignore the fact that it doesn't recognize the function instead of raising the Expression.Error?  Because once the formula is back in Excel it appears to work!

Hi @dbooksta ,

 

When you import data in PQ, PQ purely loads whatever data you have. One thing PQ doesn't like is #N/A, #REF!, etc. I am referring to source data. Once data is imported, you can load the data into excel, and then apply the add-in function - LastPrice(), but don't add it between columns in the loaded table. Instead, you can add columns at the end of the table and apply the add-in function.

 

Please note that LastPrice() is an add-in for excel (I assumed it is a VBA custom function) not for PQ, so the solution is to replicate the formula in PQ, providing we know the logic of LastPrice(), then we should be able to replicate it.

 

I hope the explanation is clear.

 

 

Regards

KT

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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