PowerQuery custom columns with formulas

[lordneeko]

I want my output table from my powerquery to be able to have custom columns that (after the query creates the table) can fill in data from other locations within the workbook.

I have created a custom column with a properly formatted function
like
="='Sheet2'!$B$2"

But, when the query runs, it fills in that column with the text of the formula, but doesn't calculate it. If I then select the cell, press F2>F9, it'll calculate it.

Any way to have this come out as a real formula and calculate like it is supposed to?

[CK76]

Nope. PQ can return only specific data types. Binary, Date/DateTime, Text, Number (Int64, decimal) etc. None that will be returned as formula.

But why do calculation on the sheet? You can query both data into PQ and perform your calculation in PQ's M code.

Or alternately, load both to data model and use DAX to perform calculation.

I'd recommend uploading sample workbook demonstrating your need. To upload use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window to manage uploads.

[lordneeko]

well...I'll tell ya there are a lot of Excel features that I cannot use. Either because of functions being turned off by enterprise, or because the application will not function under the weight of significant anti-malware type measures.
Quite often, just opening the Data Model is enough to crash Excel...and at one time, the data model feature was disabled by enterprise. And I'm not sure it won't be done again. So, I'm trying my best to create our solution avoiding VB (would make my life so easy) and other "advanced" features....which is why tyring to get as much done inside the formula is so important to me

Stupid I know...that I have to find so many work arounds...but that is my life.

I have success with PQ, and I'm still learning how to use it effectively.

I'm unsure how to get data from two separate data sources into the same table within PQ.

Essentially, I have the data sources. I have my main data table and another table that I need to query, based on a column in the data table, and pull the correct value out of that table

I'll see about perhaps trying to upload something..but it would take me a long time to recreate the structure and sanitize the data.

[CK76]

Let's say that you load 2 table separately. There must be some key column that ties two tables together (at least one table must have unique values for the key column).

Then you'd use Merge Query tool within PQ. And use Left Outer Join (most typical type) or other join to merge tables together. Then expand the joined table for the specific column that hold the value that you are interested in.

[lordneeko]

The reaosn I need a "calculated" column is because I need an "IsVisble" column in my output table. e.g. =SUBTOTAL(3,A2)

I can manually ad an adhoc column to the table's output, and that persist with no problem. But if a data refresh comes along that adds/deletes a row of table, it'll also get rid of that enttry in the calculated column

Thoughts onthis?

[CK76]

Oh, so it's for the subtotal. Then what you can do is one of following.

1. Use VBA to force calculation on the table after load.
2. Have dynamic named ranges set up and use that to set calculation ranges etc.

These two comes to mind.

[lordneeko]

blast it! lol You just went where I cannot go..VBA

I mean I CAN, but then the file becomes local only, and I cannot upload it,and use it, on our sharpeoint page. (they block macro enabled office files)
If I could do VBA, I'da had this done a long time ago lol

See, I can create the calculated columns...but a data refresh "might" wipe it out. :\

I handle refreshing the data within the Excel sheet by laucnhing it using PowerShell. You think there is a way I can see it to calculate those ranges using a Powershell function?

I currently launch it with this script (nevermind I tried to put it in there, but it is blocking saying I cannot post HTML to the site forumns)

basically it opens it, and runs this.

Please Login or Register  to view this content.

[CK76]

In that case, you can use PowerShell to add formula to Excel.
Make it refresh all first.... then find last used row & col (offset col by 1).
For an example...

$ws = $wb.Worksheets.Item(1) 
$rng = $ws.UsedRange 
$lRow = $rng.SpecialCells(11).row 
$lCol = $rng.SpecialCells(11).column + 1  

Depending on where you start your data range. Use first row of the data range to lRow in lCol and add formula.

$ws.Range(Cells(2,$lCol),Cells($lRow,$lCol)).Formula = "=SUBTOTAL(3,A2)"