Updating an existing PowerQuery formula?

Puck101

New Member
Joined
Jul 22, 2015
Messages
9
Hi, is it possible to simply update an existing power query with no knowledge of how they work?

I have a calculation file which I inherited and want to update it to account for the current year and am a complete novice when it comes to power queries and power pivots.

Currently, I have added in the updated tables and edited the queries to take account of these. I believe this to have worked correctly as I can see the data in the pivot table, however the formula used to update my file is not bringing anything in. I have pasted the formula below?
[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[TOTAL REPORTED SEGMENTS]","[Calendar].[Year].["&YEAR(CF$1)&"]","[Calendar].[Month Number].["&MONTH(CF$1)&"]","[COGNOS TOTAL Q1 2015 - YTD].[CONCATENATION].["&$A2330&"]")+0 [CF1 = a date & A2330 = A unique identifier]

Is anyone able to spot any obvious error with it? It just N/As and I've no idea why. The formula from the previous column looking at the previous month works fine.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I don't know if this helps anyone provide any advice but I've noticed in the pivot table that the year is listed but for 2019 it is blank? Could this be the issue? When I look at the data source it is coming from a seperate table to my data source titled Calendar and this is based on a connection query. I'm wondering if the date range needs to be updated elsewhere to include 2019 but I'm unsure?
 
Upvote 0
The CUBE formula is not part of Power Query or Power Pivot, it has been with Excel since Excel 2007. But it is hard to update anything if you don't know what its is doing or how it works.

Can you post the workbook somewhere so we can see what we are working with?
 
Last edited:
Upvote 0
Hi theBardd, thanks for replying and your offer of help but I've appeared to crack it via trial and error. I found a connection table in Manage data which I needed to extend the date range, once including 2019 into this, the CUBE formula started to work.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top