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?

<colgroup><col></colgroup><tbody>
</tbody>
=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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Puck101

New Member
Joined
Jul 22, 2015
Messages
9
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?
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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:

Puck101

New Member
Joined
Jul 22, 2015
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,324
Messages
5,449,729
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top