SOLVED

Cannot pull values in from Salesforce report using Get External Data

%3CLINGO-SUB%20id%3D%22lingo-sub-1443136%22%20slang%3D%22en-US%22%3ECannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443136%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20Get%20External%20Data%20to%20connect%20to%20a%20Salesforce%20report%20--%20all%20of%20the%20fields%20are%20fine%20EXCEPT%20the%20value%20fields%20which%20show%20as%20%22Record%22%20in%20the%20query%20itself%20and%20simply%20don't%20appear%20in%20the%20table%20that%20imports%20to%20Excel%20--%20has%20anyone%20seen%20this%3F%20I%20have%20admin%20rights%20to%20Salesforce%2C%20so%20I%20don't%20think%20this%20is%20a%20data%20rights%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1443136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443207%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690293%22%20target%3D%22_blank%22%3E%40Joanne_Thiel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20work%20with%20Saleforce%20itself%2C%20but%20that's%20absolutely%20okay%20when%20query%20returns%20column%20of%20records.%20Usually%20that%20linked%20to%20this%20records%20table%20data.%20You%20shall%20transform%20these%20records%20in%20Power%20Query.%20With%20this%20records%20could%20be%20lists%2C%20etc%2C%20-%20do%20transformations%20till%20you%20receive%20what%20you%20need%20as%20values%2C%20remove%20all%20unused%20columns%20with%20records%20and%20lists%20and%20only%20after%20that%20return%20result%20to%20Excel%20sheet.%20Keep%20only%20values%20in%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443229%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20replying%20--%20did%20you%20happen%20to%20look%20at%20the%20screen%20shot%20I%20had%20attached%3F%20The%20columns%20that%20should%20have%20%24values%20in%20them%20simply%20say%20%22Record%22%20in%20each%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443262%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690293%22%20target%3D%22_blank%22%3E%40Joanne_Thiel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOf%20course.%20Word%20Record%20is%20aligned%20to%20the%20left%2C%20highlighted%20and%20clickable.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20403px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196742iCE7D825643F9D611%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20click%20in%20the%20cell%20on%20empty%20space%20to%20the%20right%20of%20the%20word%20and%20wait%20a%20bit%2C%20at%20the%20bottom%20of%20the%20screen%20you%20will%20see%20another%20window%20which%20shows%20what%20is%20within%20record.%20It%20usually%20list%20with%20names%20and%20values.%20However%2C%20could%20be%20simply%20null.%20If%20click%20on%20two-arrows%20icon%20on%20the%20top%20right%20of%20the%20column%2C%20you%20expand%20all%20records.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERecord%20means%20that%20Revenue%20has%20not%20only%20value%20inside%20but%20some%20other%20metadata%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443269%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you!%20That%20worked%20perfectly%20--%20the%20column%20had%20both%20an%20amount%20and%20a%20currency%20associated%20to%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443332%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20pull%20values%20in%20from%20Salesforce%20report%20using%20Get%20External%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690293%22%20target%3D%22_blank%22%3E%40Joanne_Thiel%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Using Get External Data to connect to a Salesforce report -- all of the fields are fine EXCEPT the value fields which show as "Record" in the query itself and simply don't appear in the table that imports to Excel -- has anyone seen this? I have admin rights to Salesforce, so I don't think this is a data rights issue.

5 Replies
Highlighted

@Joanne_Thiel 

I didn't work with Saleforce itself, but that's absolutely okay when query returns column of records. Usually that linked to this records table data. You shall transform these records in Power Query. With this records could be lists, etc, - do transformations till you receive what you need as values, remove all unused columns with records and lists and only after that return result to Excel sheet. Keep only values in it.

Highlighted

@Sergei Baklan Thank you for replying -- did you happen to look at the screen shot I had attached? The columns that should have $values in them simply say "Record" in each cell.

Highlighted
Solution

@Joanne_Thiel 

Of course. Word Record is aligned to the left, highlighted and clickable.

image.png

If you click in the cell on empty space to the right of the word and wait a bit, at the bottom of the screen you will see another window which shows what is within record. It usually list with names and values. However, could be simply null. If click on two-arrows icon on the top right of the column, you expand all records.

 

Record means that Revenue has not only value inside but some other metadata as well.

Highlighted

@Sergei Baklan Thank you! That worked perfectly -- the column had both an amount and a currency associated to it!

Highlighted