At last!!! Power Query within Power BI Report Builder!
Вставка
- Опубліковано 30 вер 2024
- With Power Query being available for Power BI Paginated reports in Report Builder, you can now do something that was very hard to do in the past. Patrick shows you what's new!
Get data with Power Query available in Power BI Report Builder (Preview)
powerbi.micros...
Connect to Snowflake, Databricks, and others with Power Query Online in Power BI Report Builder
learn.microsof...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #Paginated #GuyInACube
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
I have been using SSRS for a long time, and always felt like Microsoft has neglected its development. Although, 70% reports I create are in SSRS. So this is a fantastic update.
Took long enough for this update!!
Hi Patrick, Can you help with a video on Connecting to Power BI dataset in Report Builder with this latest features? Especially I'm facing difficulties creating Query Parameters and linking them with Report Parameters. Thanks!
Interesting. We're way behind the times in my workplace. We've been using SSRS for about the last 12 years or so, and over the last 5 years or so, a couple of the team members have been using the SSRS mobile reports to create dashboards for the staff to use. Those mobile reports are now deprecated and removed from the latest versions, and now that I'm back in the team (after moving around a few different departments while this was all going on), I've managed to convince the college to install a Power BI report server. We're just at the start of the journey of rebuilding the data warehouses and will be building Power BI dashboards to replace the mobile reports, but will still be making heavy use of SSRS. Having Power Query built in there now will open up quite a few new avenues to explore.
Wo! This is even better than the new Clicky, Draggy, Droppy replacement for Report Builder in the Fabric web interface! Wait hold on. How do we use this with parameters? We need this to work with single and multi-valued parameters.
Yes you can pass report parameters from dataset parameters tab to power query. You need to create parameters in power query first and map the values in dataset parameters tab.
🎉🎉🎉 finally . Thats really helpful to simplfy paginated report datasets structures . however looking at performance perspective i am curious how it will work. Basically merge is expensive power query activity which is going to replace lookup function that is already not reccommended from best practice point of view. So which one is better power query merge or lookup?
The best scenario will be to do join in power query in a way that supports folding. If you break PQ folding, the performance is likely to similar to doing it in PQ or the paginated report.
Was happy. Now sad. Limitations for Snowflake. Gateway data sources aren't supported even though you connect in Mashup Editor. Still love SSRS though and looking forward to more improvements to the paginated reports UI
#PowerQueryEverything !!!
Not actually a Paginated Report user myself. I can understand Patrick's excitement seeing of where we're coming from and what is added. But frankly, I'm actually astonished to learn paginated reports doesn't support linking in a data model like Power BI does. What archaic solution is this to have to merge in power query. I feels like a one-table pivot table in a world where such a thing like Power Pivot exists.
I'm not touching Paginated Reports as long as it is not up-to-speed.
I liked Patrick's excitement though 🙂 Go Patrick !!
It does but you have to create the datamodel elsewhere then link to it.
No more publishing a regular Power BI dataset and using that as a source 😅
Thanks for the heads up, Patrick :) I've been waiting for that feature for sooooooo long in PBI.
Q: I have connected to Snowflake (Power Query) in Power BI Report Builder. I have created the Report Parameters and Parameters successfully but the parameters don't work at all while rendering the report. Please suggest what I should do to achieve it ?
Sadly, useless for querying a semantic model, which is the prime use case for Paginated Reports.
"Why not use the lakehouse behind the SM?"
Because not everyone has full Fabric but everyone using Paginated Repoets has access to PBI Semantic Models.
But the limitation of power query in terms of dataset is that whatever is your last table in the mashup will be the dataset created in your paginated view. In your use case since the last table was DimProductCatogery so you'll see that table as a data set instead of Sales. In order to have Sales as your data set you need to move it down in the list
At last!!! BUT connecting to SQL server data source is not working by clickin Get data?!?! It cannot connect, but if I use SQL server button from Data tab it works?
Error output: An exception occurred: DataSource.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
different port meybe or how to solve?
Hi, i'm looking for a simple solution to replace the null value with 0 without using "IIF(IsNothing(", there is any solution for that in Power BI Report Builder ?
Q: how did you get snowflake working with paginated reports? I have tried connecting via the native snowflake client, but it doesn’t work. Do you have a gateway setup for your snowflake connector? I can’t get it to work with snowflake. It is bizarre. Did you have to do any special setup or anything? I have gone through all the docs Microsoft has published and followed those instructions, but I am unable get the data to show up.
Yes yes yes yes yes YES! No more overcomplicated DAX queries for simple tasks.
Maybe. Expect I didn’t see Power BI semantic data models as a listed source for Power Query.
Although maybe if you do OneLake storage for your semantic model that could expose the underlying data!
Just thinking out loud. All of this is so cool though 😊
I’ve been playing around with this quite a bit, but it’s still very early. One of the things I found is that while you cannot connect directly to the semantic model, you can connect to it as an SSAS Server via the XMLA endpoint.
Newbie here...how is this different than using transform data button and making the table there? Thanks.
Ah so excited!!!
Love how excited you get about this stuff!
What version of Power BI Report Builder has Power Query?
ive been waiting for this.... writing Dax in that editor was PAIN. Thank you PBI Team 🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌
This is so exciting! SSRS's limitation in our environment has pretty much eliminated that as a solution, and PBI Report Builder not being able to do anything close to what PBI does pushed me to Excel and it's not great for print outs which is what we need. This is a game changer! I CANNOT express how much I'm geeking out about this.
Oh this is Amazing Patric! Really appreciate guy In a cube channel for educating on every new feature for fabric and powerBI
I have quick question can you make video on using postgresql query with parameters stored in SQL
How that will work in paginated report
I am here for knowledge but is that Forest Hills Queens in the background?
Probably over complicating it, but would it be better or worse to (in m) to have 3 let uhm statements, the one fetches the one datasource, the one fetches the other and 3 one combines so its all on the same query. I suspect this will only help those with 3 large datasets to join as it will keep the model smal, perhaps one doesnt need to worry about model size in paginated reports
Is it possible to use report parameters if importing the data like this? I can't see how to modify the query.
Its requires you to be signed into a service account and it does not appear to work at all for gcc clients. Throws errors for gcc clients. Also why is it requiring anyone to sign in to use this feature. Power Bi does not require you to sign in to use Power Query.
GCC clouds should be supported later in the preview period / toward GA.
Power Query More of the Things. Really rather use it than Notebooks in general.
Still can’t join two csv files on OneDrive due to privacy issues.
currently doesn’t allow oracle connections via a gateway. hoping this is added
#GameChanger for real!
this is a fantastic update - had been doing a lot of circus to get this done; now its better
Awesome tips we needed it long time. Thanks
How does this impact Publishing the report to Power BI Service? Is the semantic model published just like with PBIX Power BI Reports?
Looking forward to this update.
In this scenario, there is no semantic model created or required. The paginated report uses power query to directly connect to the source and issue the query when you open the report. This is analogous to direct query in PBI reports.
Great video, thanks.
Great update! The only limit I found was the inability to write custom queries for a Snowflake connection. This is possible within power bi desktop data connections, so hoping this functionality is added as a new feature.
An additional issue I've had is recognizing the Semantic Model within Power BI Service. This creates a problem when attempting to add the Report to an App, and then subsequently giving users permissions to view the report.
How cool is that! Bananas! 🤠
What about report parameters?
You can pass parameters from dataset parameters tab.
Whaaaat? This is bananas
This is powerful
Really amazing 👏!
damn
How to Pass Parameters to M Query?
I figured it out
Finally!!