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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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