Results 1 to 4 of 4

Thread: Updating an existing PowerQuery formula?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Location
    Farnborough, UK
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Updating an existing PowerQuery formula?

    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?
    =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.

  2. #2
    New Member
    Join Date
    Jul 2015
    Location
    Farnborough, UK
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Updating an existing PowerQuery formula?

    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?

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Updating an existing PowerQuery formula?

    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 by theBardd; May 14th, 2019 at 06:34 AM.

  4. #4
    New Member
    Join Date
    Jul 2015
    Location
    Farnborough, UK
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Updating an existing PowerQuery formula?

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •