I have a question about how to send data to Excel.
I tested the Excel Connect and the Power BI Connector, and here is the thing:
When we link a stream to PowerBI, we access the database in “Transform Data”, and we can play with the data (in the Power Query Editor) to have the final dataset look like we want, that is amazing.
In Excel, you can in theory do the same thing in the Power Query Editor when you load data from the web or from a database.
Do you guys know how to do that in Excel ? Cause the current Excel-Speckle-Connector is doing something else if I am not wrong. Would be great to be able to call a function like Speckle.GetByUrl(\https://speckle.xyz/streams/42b4e93922) in the Power Query Editor from Excel. (as we do in powerBI). Then we would be able to modify the database from its origin before having it as Excel columns and lines.
This is exactly what I am trying to do
I cleaned and formated the dataset which came from Speckle into the Power Query Editor in PowerBI. And I would like to do the exact same thing in Excel. But I cant send my data from PowerBI to excel.
To answer your question, what I want at end, besides the nice PowerBI Dashboards, is a formated Excel Spreadsheet (columns and line size, colors, data organization etc…), gathering data from my stream, and updating itself when a new commit comes, without having to redo the entire spreadsheet formating. That is exactly what I have in PowerBI… But the thing is, it needs to be in excel format… haha
Does all of that makes sense to you ?
Basic data management stuffs But from a speckle stream datasource
Without having tested this, and assuming that PowerQuery support in Excel is exactly the same as in PowerBI, I have a super hack for you to try (I you’d wish to accept ):
All the PowerBI DataConnector code is written in PowerQuery M and is all under a single file. You “could” try and just copy all of that over to the Excel PowerQuery editor and try to use the functions defined there, such as Speckle.GetByUrl()
I don’t have Excel on my machine to give this a go, but I thought it may be worth mentioning it
Also potentially doable, is saving the entire file as a .pqm file, and loading it into your PQ with Extension.Contents(), though I’m not sure that’s available in Excel’s PQ
That could actually be a solution
It is more of a temporary solution because you would need to (1) actualize your Speckle Stream in PowerBI in order to (2) have it in excel, but it works.
Would be super nice to be able to actualize directly the stream in Excel though ! But I am sure it is not that complicated as everything is already coded in Power Query. I know you guys will come with an update soon
it doesn’t quite work because the Excel access to the powerBI datasets is linked to the dataset published online on your SharePoint-PowerBI profile… and… the online power bi report or dataset isn’t automatically updated when a new commit comes in the stream (contrary to PowerBI Desktop), because Speckle is a custom connector (see feed below).
So I guess we are back to square one would be nice to implement all the power query functions developed for PowerBI into the Excel Connector, to be able to read the speckle data stream and play with it directly in excel. (to have a dynamic excel table that updates itself automatically when new commits come).
Not really except from what’s already been discussed here in the firm!
Primarily because we’re quite busy with the other connectors, but we’d love to hear about your needs and see how we can fit them into our future roadmap