Every once in a while, I’ll Google something and get 0 results. I hate that.
This time, it was me trying to connect to a Power BI data model through Excel so I could use the cube functions and make a fancy little Excel-based dashboard.
I swear, I don’t know how anyone does anything in life with things being so difficult. But I digress.
Things I tried to troubleshoot
There were a couple of things I did.
I Searched Google (see above).
It didn’t work.
I checked the event viewer.
I wasn’t real sure which event viewer node to look into, so I did my little event viewer trick – I created a new custom view called “All events”
As you can see above, I created an event viewer view that displays all warning and error events that happened in the last hour from all the event logs. I do this when I don’t know which log or source my event is going to pop up from. Pro tip: make sure you limit it to the last hour, or the event viewer will have a conniption and not work.
I didn’t find anything there, though, either.
I fiddled around with things.
It was really weird. I just started using a different laptop, and inserting a PivotTable was just working last week. I assumed it had to be machine specific or something with Excel, so I dug a little deeper.
After logging out of all my profiles and trying to see if that made a difference (it didn’t), I started looking at the data connection that was made. You see, when I tried to add the PivotTable, Excel said it couldn’t, but it still managed to create a data connection. The connection would show up when I used the CUBESET formula, but it wasn’t working, and I wasn’t getting any error messages.
I’m not sure exactly what I ended up doing, but I did eventually get another error message from Excel when trying to use CUBESET. It said “forbidden”. Enter Chat GPT.
I used Chat GPT – this one was successful
The data connection that was created had a connection string. I was hoping that I could debug it a little bit and maybe make some changes in order for the darn thing to work. I explained my situation, copied and pasted it into Chat GPT, and then worked through the results.
The first thing that popped up with “update the identity provider URL.”
I did that. Once I did, and tried the CUBESET formula again, I got a new error message (progress!) – “Microsoft Excel: Errors in the OLE DB provider. The specified Power BI workspace is not found.”
It also referred to my dataset by name. Hmm. Well, I tried changing the name of the dataset. Nope. I tried making the dataset premium by user. Nope. I tried connecting to a DIFFERENT dataset. Nope.
I popped that info back into Chat GPT and got the following response:
That did it. I followed the instructions to find the appropriate dataset_id value and then tossed it in the connection string. Voila!
Hope this helps. I’ll write a post about the cube functions in an upcoming post.
Leave a Reply