none
Currency fields in Salesforce reports display as "[Record]" in Power Query RRS feed

  • Question

  • Currency fields in Salesforce reports display as [Record] in excel when using power query.    Can include screenshots due to forum rules.

    Wednesday, February 11, 2015 2:07 PM

Answers

All replies

  • I believe they are records in order to include both the amount and the currency symbol. You should be able to expand them in the editor.

    Wednesday, February 11, 2015 5:29 PM
    Moderator
  • Could you be a bit more specific please ?

    I'm experiencing the same issue, and clicking on the cell only shows "[Record]" ?

    Wednesday, February 11, 2015 7:04 PM
  • Thanks - that helped me figure it out.

    IF you right click on the source and click edit to get into edit mode on the query, then you have the option to expand the record.  Doing this and saving does the trick.  

    Will try and post some screenshots later if others run into this.

    Wednesday, February 11, 2015 7:52 PM
  • That's right!

    Steffan, let me know if you need more clarification.

    Wednesday, February 11, 2015 8:03 PM
    Moderator
  • We've gotten this feedback a few times and will try to figure out a way to change how the data is returned without breaking backwards compatibility.
    Thursday, February 12, 2015 1:33 PM
  • I have this issue, but don't seem to be able to resolve it in this way. My Salesforce report is a pipeline report with 15 columns describing the opportunity (name, owner, target date etc) and then 11 currency value fields summarising the values  associated with the opportunity: 1 "Total Opportunity" amount, 4 "Amount by type" columns (licencing, hardware etc) and a final 6 columns totalling the revenue projection totals for each of the next 6 years ("2015 amount", "2016 amount" etc).

    When I run this report into PowerQuery all these 11 columsn retuns as "[Record]". When I edit the powerquery, each of the fields is a "Record" navigation link. If I click any of these drill-downs I simple get a new 2-row table with the value and currency that replaces the entire dataset, and an option in the "record" ribbon to "convert to table".

    What I realyl want to happen is for the main report to identify the 11 columns as either 11 string values, which I will write steps to split into the currency code and the value (e.g. "GBP123000" converts to "GBP", "123,000") or for it to automatically do this step for me (which is what the "Export to Excel" button in Salesforce actuall does).

    I woudl love it if someone could point out an easy mistake that I'm making in here somewhere though? I'm desparate to get this solution working as Salesforce's reporting capability to near-useless for any kind of dash-boarding and I don't want to have to be stuck exporting a report any refreshing an Excel based report manually for the end user every time they want to update it.

    Any help gratefully received.

    Chris

    Friday, May 8, 2015 2:57 PM
  • Don't click on the "Record" text. Click on the "expand" icon that's in the header of that column and you can expand out the two components of the currency.
    Friday, May 8, 2015 2:59 PM
  • Awesome, thanks Curt. I was being a bit stupid wasn't I. I had merely glanced at that button and assumed it was the same drop-down filter button as all the othe columns. I'd entirely missed the subtle "expand" arrows.

    All perectly clear now, and exactly what I needed to do. You have absolutely made my week! I love Power Query :-)

    C

    Monday, May 11, 2015 11:59 AM