none
Project GUIDs changed in Excel 2013 PowerPivot datamodel? RRS feed

  • Question

  • Hi Forum users and Project Server Guru's alike,

    I have an Excel sheet that connects to the Projects OData feed. In the Excel file I have a sheet that shows a table with the raw Projects data. This includes a colum with the Project ID (or Project GUID if you prefer).

    I created another sheet that uses VLookup formulas to search the table for specific data related to these projects in the table on my source data sheet. I also have a cel that has a named range field called "ProjectUID".

    After creating this file I was able to upload it to Project Server 2013 (on Prem). And after that I used the Querie string webpart that filtered "ProjectUID" and a excel services webpart that connected with the queriestring to show data relative to a specfic project on a PDP.

    This worked fine untill recently...

    Please view the image, it shows the change in the file (luck has it I saved a 0.9 version and a 1.0 version on the server). I have no clue how the file suddenly shows { } symbols on the Project ID cells. I have no recollection of changing the data.  But I do know that they are messing up my VLookup formulas  and they will not work with the "ProjectUID" value from the Queriestring because the queriestring will produce a value without the { } symbols. 

    Picture of the values that changed overnight.

    Is there any way to know why the values changed? And is there a way to change them back? Or do I need to change all my formula's to incorperate { } symbols?

    Kind regards,

    Erik van Hurck

    Tuesday, January 14, 2014 3:51 PM

Answers

  • Made the formula working :-)

    =SUBSTITUTE(SUBSTITUTE(Tasks[ProjectId];"{"; ""); "}"; "")

    Wednesday, April 15, 2015 8:59 AM
  • Hi Erik,

    Indeed it looks strange. I guess you already checked for any recent operation on your instance that might have triggered this behavior.

    It might take more time to figure out what happened, whereas changing your formula to incorporate {} symbols will take you a couple of minutes..

    Anyway it could be good to identify what happened since if it happened once, it could happen again.

    An advice could be to take into account in your formula both cases : with and without {} caracters.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, January 14, 2014 3:59 PM
    Moderator

All replies

  • Hi Erik,

    Indeed it looks strange. I guess you already checked for any recent operation on your instance that might have triggered this behavior.

    It might take more time to figure out what happened, whereas changing your formula to incorporate {} symbols will take you a couple of minutes..

    Anyway it could be good to identify what happened since if it happened once, it could happen again.

    An advice could be to take into account in your formula both cases : with and without {} caracters.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, January 14, 2014 3:59 PM
    Moderator
  • Hi Guillaume,

    Indeed I have checked all there is to check and haven't come with a reason why it changed.

    I believe I have no other choice than to edit all the formula's and change the worksheet like you said.

    Thank you for the response.

    Erik

    Monday, January 20, 2014 8:54 AM
  • I have the same problem. In one connection (Projects) I get the project GUID without brackets {}. In the connection for getting the tasks I get the project GUID with brackets.I use the ProjectId in a connection and for aggregating task-information in a pivottable.

    So what is going wrong here and how I can workaround this?

    Thanks in advance

    Karl

    Wednesday, April 15, 2015 7:20 AM
  • Hi Karl,

    I have not seen any definitive solution yet, I created a PowerPivot formula where I said if it has brackets; remove those and if the GUID don't have them leave the value as is.

    And after this I linked to that formula field instead of the out of box GUIDS.

    Wednesday, April 15, 2015 7:55 AM
  • Thanks, May I ask you to post the formula you are using as I tried the same by using SUBSTITUTE but it doesn't work

    Thanks!

    Wednesday, April 15, 2015 8:13 AM
  • Made the formula working :-)

    =SUBSTITUTE(SUBSTITUTE(Tasks[ProjectId];"{"; ""); "}"; "")

    Wednesday, April 15, 2015 8:59 AM
  • Great Karl, best of luck.

    Wednesday, April 15, 2015 1:28 PM
  • Thanks, getting the same nonsense right now (2016-02-04) in a workbook that I created 3 days ago. 2 project table imports (hierarchy), 1 with and 1 without brackets. Even a refresh will not fix it.
    Thursday, February 4, 2016 8:03 AM
  • Hi Adrian,

    Did the formula Karl Pe shares with us work for you? I haven't seen the issue arise lately so I can't do any testing.

    Thursday, February 4, 2016 8:06 AM
  • Hi Erik,

    yes the fix works, should have mentioned it, it was just too obvious.
    I'll monitor my reports and likely just wrap every GUID join with a calculated column to remove the brackets if they appear one day.

    While we're at it: Don't use Excel 2016 for data refresh, it can/will drop decimal separators. Do 1,25 hours become 125 hours. Just a PSA.

    Best regards,
    Adrian

    Thursday, February 4, 2016 10:30 AM
  • Haha, didn't remember you from Barbara's thread over there.

    FYI: The report was created partially with 2013, partially with 2016. Data Refreshes were made in 2013 only. So maybe the creation in Excel PowerPivot is the issue here.

    Also: Thanks for poitning out PowerQuery issues.

    The German word is "Galgenhumor"

    While we're at it:

    • VBA Ribbon SetCustomUI crashes Project 2016: https://social.technet.microsoft.com/Forums/projectserver/en-US/8ca35061-6f6b-49bc-a5f5-779d5e431d83/project-2016-vba-setcustomui-project-crashes-as-soon-as-you-mouseover-a-button?forum=projectprofessional2010general#8ca35061-6f6b-49bc-a5f5-779d5e431d83
    • Office 2016 + SharePoint 2013: Editing documents often seems to leave them in a locked state (cannot checkout/move/edit)

    One more: Trying to edit an OData connection in Excel results in the "must be edited in Power Pivot" message. Trying to edit in PowerPivot fails due to PowerPivot not being able to Authenticate to O365 OData

    Thursday, February 4, 2016 1:31 PM
  • =LOWER(SUBSTITUTE(SUBSTITUTE(Tasks[ProjectId];"{"; ""); "}"; ""))

    Throw in a LOWER to put it all into lower case.

    It may save one you another few hours or bug hunting.

    Friday, February 5, 2016 3:02 PM