Using Power Query to connect to Speckle inside Excel

BaudouinNP · 26 October 2022 13:36

Hey everyone,

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.

I think that would be interesting
@gokermu ?

@dimitrie, I saw a post from 2019 where you kind of explain something similar (Speckle and PowerBI with Excel - #8 by dimitrie)

Thanks a looot as always !
I would be glad to discuss that topic with you !!

jonathon · 26 October 2022 14:30

Hi Baudouin,

We are reviewing the capabilities of both these connectors, and bringing functional parity between them is a good call - where possible.

I’m going to take a look into this and get back to you soon.

teocomi · 26 October 2022 16:13

Maybe we could find a way to get the PowerBI Speckle Data source work in Excel as well…
What are you trying to achieve @BaudouinNP ?

BaudouinNP · 27 October 2022 13:30

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

AlanRynne · 27 October 2022 13:45

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

github.com

specklesystems/speckle-powerbi/blob/8844f1837e302722e0cad3abfdeec2ae2b4bb236/Speckle/Speckle.pq

section Speckle;

Beta = true;
Category = "Other";
Icons = [
    Icon16 = { Extension.Contents("SpeckleLogo16.png"), Extension.Contents("SpeckleLogo20.png"), Extension.Contents("SpeckleLogo24.png"), Extension.Contents("SpeckleLogo32.png") },
    Icon32 = { Extension.Contents("SpeckleLogo32.png"), Extension.Contents("SpeckleLogo40.png"), Extension.Contents("SpeckleLogo48.png"), Extension.Contents("SpeckleLogo64.png") }
];

GetPublish = (key as text) as record => [
    Beta = Beta,
    Category = Category,
    ButtonText = { Extension.LoadString(Text.Format("#{0}.Title", {key})), Extension.LoadString(Text.Format("#{0}.Label",{key})) },
    LearnMoreUrl = "https://speckle.guide",
    SourceImage = Icons,
    SourceTypeImage = Icons
];

Speckle = [
    Authentication = [

This file has been truncated. show original

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

BaudouinNP · 28 October 2022 15:05

Hey,

Looks like a good idea!! But I couldnt make it work… I probably didnt manage to make exactly what you meant…
Should I just copy paste the code as it is ?

Thanks a lot for your answer !

BaudouinNP · 2 November 2022 10:43

Hi,
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

BaudouinNP · 8 November 2022 14:24

Update:

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).

JdB · 9 November 2022 11:33

Recently, quite a number of Excel connector feature requests are popping up

@teocomi @connor out of interest, do you have any rough plans or ideas for the Speckle Excel connector that you are able to share?

teocomi · 9 November 2022 14:19

Hey @JdB ,

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

bfortunato · 12 May 2024 15:19

I think having the excel connector use power query would make a lot of sense. Powerquery is standard on excel from what I understand. PowerBi is a separate license. We have both, and I just import into PowerBi and then export to excel. The native flattening workflow Excel | Speckle Docs is not really robust enough to handle the kind of highly nested data that comes out of any CAD application.

For people who don’t have a license to PowerBi (which is not cheap) it would be good to integrate the PowerQuery function into excel.

gokermu · 12 May 2024 17:58

Hey @bfortunato @BaudouinNP @JdB ,

We did some preliminary research into how we can implement this. Turns out Power Query for Excel does NOT support custom connectors. Documentation for Power Query custom data sources points strictly into Power BI.

bfortunato · 12 May 2024 19:43

Microsoft likes to make our life difficult. I guess the only way to really utilize this workflow is with Power BI. To replicate this functionality in your connector would be a heavy lift. We have Power BI so it works for us. I was just thinking of people who don’t use Power BI and will have to buy a license. Thanks for getting back to me.

jonathon · 12 May 2024 20:01

It is possible to use Python within Excel, and we also have helper functions that could “easily” port from PowerBI connector that can leverage specklepy

Fair warning: I haven’t attempted this.