Including Excel formulas in custom columns

[dbooksta]

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:

Name	Group	Weight
Apple	Fruit	.5
Banana	Fruit	.6
Carrot	Veggie	.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: 

Name	Rank	Group	LastPrice	Weight
Apple	10	Fruit	3	0.5
Banana	0	Fruit	4	0.6
Carrot	0	Veggie	5	0.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)?

[Snarglefarg]

Try this:

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

[KT_Bsmart2gethe]

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 

[dbooksta]

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!

[KT_Bsmart2gethe · 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