At last!!! Power Query within Power BI Report Builder!

[@hirenthanki2932]

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.

[@Jschmuck8987]

Took long enough for this update!!

[@bujin1977]

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.

[@druthorah]

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

[@walteradamsbe]

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 !!

[@ThePPhilo]

It does but you have to create the datamodel elsewhere then link to it.

[@simonvaughan5654]

Thanks for the heads up, Patrick :) I've been waiting for that feature for sooooooo long in PBI.

[@binsmistry1841]

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

[@nusretmuharrem6418]

🎉🎉🎉 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?

[@JonHeide]

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.

[@kukeleku13]

Yes yes yes yes yes YES! No more overcomplicated DAX queries for simple tasks.

[@dezcraft_dev]

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 😊

[@celsomosquera]

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.

[@McIlravyInc]

Newbie here...how is this different than using transform data button and making the table there? Thanks.

[@ThreeFourNineTwo]

this is a fantastic update - had been doing a lot of circus to get this done; now its better

[@AaronSwenson]

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.

[@pthapa55]

Awesome tips we needed it long time. Thanks

[@janakis9951]

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!

[@danrolfe7862]

ive been waiting for this.... writing Dax in that editor was PAIN. Thank you PBI Team 🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌🍌

[@celsomosquera]

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.

[@rn_chen]

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.

[@delliottflock]

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.

[@delliottflock]

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.

[@uahnbu]

Can I refer to a different Dataset from the Power Query Editor, cos a Dataset only yield one last query?

[@germanareta7267]

Great video, thanks.

[@kristynaferris-datadragonness]

Ah so excited!!!

[@cuilanzou8638]

Really amazing 👏!

[@omeromer1998]

Can you use parameters tho?

[@SerenityAP]

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.

[@JonHeide]

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.

[@ItsNotAboutTheCell]

#PowerQueryEverything !!!

[@WallDag]

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.

[@aldobot_7856]

currently doesn’t allow oracle connections via a gateway. hoping this is added

[@DataisKing]

What version of Power BI Report Builder has Power Query?

[@edgards]

Whaaaat? This is bananas

[@ThePPhilo]

Is it possible to use report parameters if importing the data like this? I can't see how to modify the query.

[@sonalisahu806]

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 ?

[@sadasiva8963]

Hi Patrick, We are using Fabric as our source to build power BI Reports. As there is a export limit 150k in Power BI, we tried to build from paginated report from power BI report builder. Still not able to solve our problem, can you please help us on this..

[@GuyInACube]

What are you running into when using paginated reports for export? Can you provide more details?

[@nouamanebrinssi6996]

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 ?

[@mariemorganhenry9072]

I am here for knowledge but is that Forest Hills Queens in the background?

[@piotr5517]

How cool is that! Bananas! 🤠

[@ankitachauhan5834]

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

[@uahnbu]

Do you know any way I can refer to another dataset from the PQ editor, i.e. I want to display both ProductCategory and Sales datasets?

[@ankitachauhan5834]

@@uahnbu the only way is you have create 2 data set for it. In one keep sales as last table and in another keep product category as last

[@uahnbu]

@@ankitachauhan5834 That's quite cumbersome. I assume that each of the 2 queries will be executed twice won't it?

[@ankitachauhan5834]

@@uahnbu yes it will be

[@harborview918]

Still can’t join two csv files on OneDrive due to privacy issues.

[@MrSparkefrostie]

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

[@jimogden1506]

This is powerful

[@hakanaltinok8709]

What about report parameters?

[@rn_chen]

You can pass parameters from dataset parameters tab.

[@Milhouse77BS]

Power Query More of the Things. Really rather use it than Notebooks in general.

[@kyleb350]

No more publishing a regular Power BI dataset and using that as a source 😅

[@prowdobe]

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.

[@JonHeide]

GCC clouds should be supported later in the preview period / toward GA.

[@Sandroider]

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?

[@tobiasosir]

Love how excited you get about this stuff!

[@junbug178]

#GameChanger for real!

[@CSHarvey]

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.

[@andolalainaratoetrandro644]

damn

[@richardpearson]

Finally!!

[@rn_chen]

How to Pass Parameters to M Query?

[@rn_chen]

I figured it out