-2

So, I'm working on developing some business apps for my organization, and I've been trying to implement the usage of the Navision Web Api for external reporting. And Unfortunately, I'm stuck using Excel because of traditional antiquated business practices.

My question - is there any way to drill-down to single values or even access the json payload through excel's web services function. I've tried switching the payload to XML on OData v4, but I can't get that to work. This is driving me insane - I cant even think about another Pivot table via odbc sql, or Odata, because I know there's a better way. I'm pretty good with PowerShell, javascript, html etc.. but Im almost clueless when it comes to VBA, and it just seems like to much suffering. Anywho, if I cant use the OData feed in the web services function - well why? Is Microsoft just trying to screw us? I keep getting a value error - naturally - because I cant drill down to a single value, format to xml, or access the json object within the web services function(if it even exists in there??) here's a link to what im looking to do Excel Services function Demo- except I cant do it - so is there any substitute or solution.

Chris
  • 1
  • 1
  • Welcome to Stack Overflow! Your question's content is [too broad](https://stackoverflow.com/help/on-topic) because it contains [too much information](http://idownvotedbecau.se/toomuchinfo), and therefore it is not well suited for Stack Overflow. Try to [improve your question](https://stackoverflow.com/help/how-to-ask) and provide a working [MCVE](https://stackoverflow.com/help/mcve). – sɐunıɔןɐqɐp Aug 01 '18 at 07:03

1 Answers1

0

So, stop whining and use power query to fetch data from Nav’s OData endpoint.

Mak Sim
  • 2,148
  • 19
  • 30
  • How do i direct single values, from a record, in the power query editor to a specific cell? I dont want to drag the data to a worksheet and run lookups. – Chris Aug 02 '18 at 00:13
  • You can remove all columns but one and paste it as a tiny table to wherever you want. It would be easier to give you an answer if you’d describe the exact task in the question. – Mak Sim Aug 02 '18 at 04:37
  • I figured it out - I just set q named range as a date parameter for my odata query, and called the object in a vlookup. Amazing. looks like I should have been doing this long ago with sql – Chris Aug 03 '18 at 04:39