Connecting Lightspeed to Power BI

jtenneyjtenney Member Posts: 2

I'm trying to connect our instance of Lightspeed Retail to Power BI so we can run customized reports ad-hoc inside of Power BI off of order data. However I'm not sure where to start or what is required to do so.


  • gregaricangregarican Member Posts: 901 

    Here's where you can get starting with the Lightspeed Retail API -->

    Hope this helps!

  • jtenneyjtenney Member Posts: 2

    How am I supposed to get authorization code if Power BI is run locally on my machine?

  • Adrian SamuelAdrian Samuel Moderator, Lightspeed Staff Posts: 654 moderator
    edited August 2019

    Hey @jtenney,

    You might consider using a custom data connector built by Microsoft like so:

    More details can be found here:

    It enables you to write code to interface between PowerBi and any data source such as the Lightspeed Retail API

    Adrian Samuel

    Software Developer

    Lightspeed HQ

  • blackballblackball Member Posts: 2
    edited December 2019

    Are you still looking for a solution to connect your Lightspeed data to Power BI?

    We've released a Lightspeed connector for SyncHub to incrementally pull all of your Lightspeed data into Power BI. Find out more at or send me a message at [email protected]

  • MichieldoMichieldo Member Posts: 1

    Hi @Jtenny,

    I was wondering if you already found a way to connect Lightspeed to Power BI? We would like to report in Power BI as well, but are facing the same challenge with limited developing capabilities within the company.

  • VintageWineGuyVintageWineGuy Member Posts: 131 

    I have been beating my head against this for a while as well. I took today and spent pretty much all day trying to come up with something I could do that wasn't super labor or tool intensive. I just had a little epiphany and breakthrough, so thought I would share.

    PowerBI (including the free desktop version) reads JSON, and can parse through the JSON that the lightspeed API produces. So, I am currently having some success with extracting a full dump of data from the API as JSON, saving it off, and feeding it to Power BI.

    The trick is mastering the right clicks in PowerBI to expand the data. Some binging turned up this video that was helpful:

    Just open the JSON file directly (the example in the video is a csv with JSON, but it helps with the concept and what to click), right click the Column, Convert to Table, then Expand the Column (uncheck the use column name as prefix option), and keep doing that for any sub-levels. Things like Price on Item you can choose to Expand to New Rows and it will give you a record for Default, Online, MSRP, etc.

    I am still experimenting, but I had been going down a path of building a data warehouse, but I may just add saving the json to my nightly batch and let PowerBI do the lifting.

  • VintageWineGuyVintageWineGuy Member Posts: 131 

    Adding on to my other post, I was working with the JSON raw in PowerBI Desktop and was getting frustrated with the speed, so I tried another trick that has worked pretty well. I used Talend Open Studio (free version) which has a pretty good JSON importer and a MySQL exporter. I took the raw JSON from the API, fed it to Talend, and pushed it to MySQL. Now that it is set up (took about 2-3 hours including clueless bumbling) I have a reporting database I can refresh periodically. A couple manual steps that will take 5-10 min to repeat, but not bad for a bunch of free tools.

Sign In or Register to comment.