Blend Power BI datasets with Excel files? INSANE AMAZING!

The moment you've been waiting for! What Marco Russo calls the holy grail of BI. Tessa Hurr walks us through the new DirectQuery over Power BI datasets feature in the December 2020 release.
Announcement Blog: powerbi.micros...
Connect with Tessa on Twitter: / tessahurr
📢 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 #DirectQuery #GuyInACube

[@NghiaNguyen711]

That magical goose bump moment when she connected the local table to the PBI DQ source !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@GuyInACube]

It was definitely magical!

[@SaniGarba]

This is the most sensible improvement done to Power BI since Power BI.

[@GuyInACube]

haha love it 👊

[@HowtoPowerBI]

Wow, what an update to end the year! Thanks for the good overview 😀 This update is gonna make a lot of people very happy

[@GuyInACube]

Thanks for the comment! So many people have been waiting for this! 👊

[@juanmoncada245]

Please help with this,thanks in advance, I have checked the preview for direct query for power Bi datasets and loaded my power bi dataset but my live connection status does not show the make changes to the model option and the other data sources are not enabled.

[@angeliquemaealconera2336]

Same problem here. I double checked my powerbi version and I already have the latest version of powerbi desktop. But I still can't add excel data after loading a powerbi dataset

[@ivanmezev]

I have the same problem. Any solution to this?

[@lk2481]

@@ivanmezev any updates? I have the same issue as well

[@ivanmezev]

@@lk2481 My PowerBi Dataset is coming from SQL Server Analysis Services and this is why not working. It seems it works for all other types but for Analysis Services.

[@kevarnold]

I showed this to my end users and they are almost as excited as I am. It could change how we build-out requirements for the EDW by testing with some static data in an extended model. Great job Power BI team!

[@GuyInACube]

Nice! I am interested in how that pans out.

[@lyonelremond4075]

This is a game changer for Power BI... Qlik & Tableau are no longer the "go to" product. Power BI is definitely becoming the new king of BI tools. Carry on the great work guys!

[@GuyInACube]

Love it! 👊

[@sudhirsinghkumar]

Adam & Patrick, you guys are awesome! This episode with Tessa on composite Models is one of the best that I have seen so far on Power BI.

[@DPFierce]

Excellent!! Please note that it appears to be (at least for me) a Preview feature that you must enable before you can add multiple datasets to a Power BI dataset. There's a Preview feature "DirectQuery for Power BI datasets and Analysis Services" that must be checked. When I first tried it, this was unchecked and I couldn't add other datasets, however after checking this preview feature, restarting Power BI, I could then add other datasets (SQL, Excel, etc.) after adding a Power BI dataset.

[@GuyInACube]

Correct. It is a preview feature that needs to be turned on in the December 2020 release.

[@raulvera705]

same problem! where is this feature to enable?! thanks!

[@DPFierce]

@@raulvera705 Go to File --> Options and Settings --> Options and a window will open. On the left hand side look for and click on "Preview Features".

[@raulvera705]

@@DPFierce Thanks a lot! :-)

[@angeliquemaealconera2336]

You saved my life. Thank you

[@alvarorodriguezlasso]

Thanks, regards from Cali-Colombia

[@sweetpea0684]

This is great! We've been waiting on this FOREVER!

[@mattl1676]

This is awesome and I can’t wait to implement this. Thank you Power BI team.

[@GuyInACube]

Woot! 👊

[@VladMZ]

Awesome progress! Now, how about letting us use multiple datasets at once, so I could finally break a bigger project apart for multiple developers and them merge them together in one report? Right now the only possibility of that is dataflows.

[@Bharathrs1985]

Woww this way of adding on top of existing model is so what we all needed

[@MatthiasK-qp9bf]

Adam is gonna explain the difference between "live connection " and "DQ" in the next guyinacube in detail , I hope :-)

[@nikagetashvili6752]

He did actually, kzbin.info/www/bejne/r2W2Xp5padagpNk

[@Babayagaom]

He explained it long back

[@GuyInACube]

Yeah probably need to revisit that based on this feature. I tried addressing it in this video - I specifically brought it up because it is confusing. You are not alone Matthias :)

[@sweydert]

Awesome video as usual but at 3:45 are the magic words to the effect of "direct query is required" followed a few seconds later by "you could have done an import." Confusing as these statements are opposites. Which one is true?

[@peterg4130]

@@GuyInACube Hey Adam, when/if you do an update, can you also talk about "data sovereignty" and how it relates to the different connection methods? I did some research, and I feel I have a good handle on it, but I'm still unsure whether any local/on-premises data is ever stored "somewhere in the cloud" with a live or DQ connection after a report has been published. A client of ours is very strict with how their data is accessed and handled, and I'm unsure whether Power BI Report Server is the only solution for them (which is missing some key PBI features), but want to know for certain before going ahead with a recommendation. Please and thank you!

[@jessmau]

Finally! My best Xmas! 🎁

[@GuyInACube]

Merry Christmas!

[@Mahruumdilse]

This is a great news!! Thank you PBI Team!

[@GuyInACube]

Appreciate the kind words! 👊

[@chayakiraneng]

Much awaited feature.. This will definitely be a game changer within our org!! Thank you PBI team :) What would be great is to be able to write a report with two or more Enterprise data model sourced into a single report, but to pick and choose entities from each. We have Enterprise models that has 60+ objects, but for specific reports we only need 5 or so objects from each enterprise data model

[@GuyInACube]

Appreciate the feedback and enthusiasm Chayakiran! I've heard that feedback before. I know it's not the same thing, but you can still hide tables from report view. That at least helps the field pane not as crowded.

[@marinaf.c.juliao2337]

One of the best updates of this year!

[@manrique4630]

This is amazing! Great work!

[@GuyInACube]

Very much agree 👊

[@rlecs]

Awesome! Just tried this for the first time now (sadly)... VERY COOL!!! Only thing I could suggest so far is a datasource grouping under "Fields"

[@scottymac734]

Lots missing here in terms of the working environment. Using Power BI Desktop, the sources buttons are grayed out. Also, the link is now to Power BI Semantic Model, along with some other options. Wish this would be updated / clarified.

[@ceromat2022]

Hi there, when I use the first dataset, all other sources of getting data are disabled .

[@praveenpatil282]

This is awesome. My clientele will love this!

[@adityap8210]

This is awesome. Solves a lot of dependency problems. Excellent feature.

[@Milhouse77BS]

Here we go!

[@GuyInACube]

Indeed!

[@nataschalaros418]

Great job! Perfect X-mas present ;) Like the color reference very much. Thank you so much!

[@b5lurker71]

I can't press the Like button any more then once, need to have it show 100+ Likes for this feature!

[@GuyInACube]

haha appreciate that Steve 👊

[@arifahmedali5341]

Hello, Amazing feature! Can we publish the new model? When I tried to published, it is giving me error. What am I missing here? Thank you

[@lewistremonti7827]

I didn’t realise this is a new feature! I did this the other day and it worked perfectly, don’t really use DQ often! Just need to sort out date hierarchies in DQ and then it’ll be brilliant!

[@misiael88]

After changing Live connection to AAS -> DirectQuery and added local Mode, I added spreadsheet. But it does not show up in Model View (it appears in report view). Why is that?

[@xpaulone]

Great! If i connect 2 dataset with their own date table, what happens? Remove one table and recreate the relationships or create a bidirectional relationship between the two?

[@sarva0483]

Can we mange the usage/users of Dataset consumed by different applications like Excel or any other applications

[@bennielopez6158]

This is awesome, can't wait to try

[@wscheung0906]

This is the most wanted one!!!!!!!!!!!!!!!!

[@chicagobassplayer7175]

After you pull in your own data and it is local, then do you publish it back up to your tenant?

[@filipbouckaert1940]

Gave it a try, adding an excel spreadsheet to a report based on Azure Analysis Services.. and it works great in Power BI Desktop. But when I publish it to Power BI Service, and try to open the report, I get the message 'There is no gateway to connect to datasource xxxx'. The orginal report works without any issue in Power BI Service The original report uses live connection, that was changed to a direct query when I added the connection to the excel.

[@MattiasGregersen]

Amazing! Please tell me that it will work with on premise Report Server as well

[@GuyInACube]

I don't know that this will work with Power BI Report Server. Will need to stay tuned on that. In general Preview features aren't supported in Power BI Report Server, so maybe we'll hear more when it goes GA.

[@gleidsonalmeida2523]

Hi, thanks for amazing content, but has been a difficult in configuration of update dataset after publish on powerbi service, i am use gateway mode default (not personal), basically after publish i need add the string connection to gateway but i need login with analysis service user and authentication windows, i try my email and pw but dont work some good soul can help me?

[@nielsvanwingerden6673]

Does this also include streaming datasets?

[@strafanich]

Super! I have a hard time now understanding what I would use a dataflow for..

[@GuyInACube]

Dataflows are a completely different thing. From a data model perspective, dataflows is just a data source. Same as Excel. There is still a place for dataflows

[@robbanerher]

Have live connection, but don't have make changes to model

[@joaoluismartins]

So amazing!!!! Already testing here and it's impressive! This will open a hole new branch of Study! Unfortunately we're not able yet to connect to on-prem Analysis Services ;(

Enfinnnnnn. Hard to wait so long to have this Key feature, a holy graal a game changer thanks for this invitation in this New dimension.

[@muzahidali12]

How about the dataset refresh in Power BI service (since it Storage is Mixed mode). Premium 48 and Pro 24 ???

[@GuyInACube]

Scheduled refresh limits didn't change with this feature. Pro is 8 times a day and Premium / PPU is 48 times a day.

[@rishirajdeb]

This is awesome👍👍. One query - though we can now relate multiple tables from multiple different sources (and having multiple connectivity modes!!), still I think it's not possible yet to combine (append/merge) data for a composite model?? (In general - what are the capabilities would be available for the power query editor?)

[@GuyInACube]

That is correct. You are just referencing the other model. There are some operations you can do like adding another column after it. But doing append/merge operations won't work.

[@shoaibahmedkhan9676]

Can we IMPORT a particular table only from power BI dataset instead of direct query?

[@thecreative100]

** Still confused!!!!!** Thank you MS and Guy in the Cube for implementing and explaining. Is this functionality available with the Power BI Dataset which is connected to SSAS Tabular Cubes? I could not connect it as of now, I tried it in two ways 1) First connecting it to Tabular Cube, where the options of connecting to a Power BI Dataset is still disabled with all the other options like it was before this update 2) Connecting to sources first where Power BI Dataset is not disabled and then connecting to Tabular cube data source, then we get the error: "The connect live option for this file is disabled because it already contains data from another data source."

[@peterclark8489]

Love it this is the great leap forward

[@msetia20]

functions like 'COUNT DISTINCT' arent supported in Direct Query is there an alternate?

[@ooooFISCHERoooo]

Cool. Christmas comes early this year.

[@saurabhsurve6152]

This is a great feature. Its allwoing me to combine data set and say excel file. But can we combine 2 PBI datasets ? Its throwing me an error. Let me know if I am missing something here.

[@ThatGuyz82]

I feel like there is a lot of Bike Sales Commerce going on based on all the bike sales data I see in PBI videos.

[@jkapoio]

when my connection to an analysis services perspective changes to DQ (after adding another data source), all tables in the model appear, instead of showing only the perspective tables.

[@phungduong4468]

Is it one limitation of Direct Query is generating traffic to server whenever an user performs any action on the published report?

[@adilmajeed8439]

How about bringing multiple data models of SAP Hana Calculation Views?

[@GuyInACube]

SAP HANA isn't supported. Not sure there are plans to do that. Appreciate the feedback though.

[@janbilek350]

This looks great! Will work also when connecting to a dataset with a RowLevelSecurity implied? Thanks

[@rvgfox]

And when we can connect to Analysis Services Multidimensional?

[@ceromat2022]

Hi, once I imported the power bi dataset my other data sources get disabled. Why?

[@MichaelDoig]

Question. We've recently started working with Data Flows for the capability of connecting different datasets. This new feature seems to negate that. So, why would we stick with Data Flows. My early glance suggests that Data Flows boasts the ability to connect to specific entities in the Dataflow; whilst Direct Query Dataset would bring down the entire model. Are there other distinct differences between the two?

[@GuyInACube]

Dataflows are a completely different thing. Someone else asked me the same thing. In the context of a company calendar. They asked it it would just be better to have a dataset with just the calendar, or have it in a dataflow and reference it. As with all things, it depends, but in this example, i'd go with a dataflow instead. Having it as a dataset and referencing that with Composite models adds unneeded complexity to something simple. It's always about picking the right tool for the job. Just because this is the new shiny doesn't mean it's the right shiny to use. Dataflows still has it's place.

[@huibvanzundert]

Very nice feature! How to move on with shared dimensions over different imported models? I have 3 models with the same date, costcentre and employee dimension. When combining the 3 models, all 3 dimensions will be doubled in the model. There is no way to delete them?

[@bertroos60]

Great! Feature great video, but the background music is very distracting ..

[@visheshjjain]

Can a composite model be published to the Service and what are the licensing requirements for composite models?

[@user-mc8gs9ig5o]

Love this video!

[@GuyInACube]

Thank you so muich 👊

[@ankurgupta842]

Much awaited feature!!! +100 Likes for this release :)

[@GuyInACube]

Love it! Thanks Ankur!

[@declanmahon1969]

Does this work connecting to a cube on SSAS, it does not seem to? but I don't have a cloud data set to try it on

[@thecreative100]

You might be right!! I believe this is still not possible, I think we are still not close to the day where we can connect to the multiple Cubes in a PBIX :(

[@psz34]

When I connect to my company dataset, then all other get data buttons become greyed out :( can it be that it's in the database settings that the BI developer of that dataset set that users cannot join other data sources to it? Or is there any way possible to overcome this if this is not the case?

[@nadsonlucio]

Hi, I got the same problem. I haven't solved it yet. Have you solved it? Anyone?

[@lk2481]

@@nadsonlucio me too any updates

[@LuisRomaUSA]

How do you reverse a composite model?

[@LuisSanchez-dw7hv]

Can i use time intelligence functions when combining differents sources like DQ and Import data?

[@iamabadger2]

Is it possible to create composite models using power bi desktop (optimized for report server) as well as the power bi service?

[@GuyInACube]

No, that's not possible.

[@rcjack3797]

I cannot add local source, data connect is grey, do i need a premium account?

[@MatthiasK-qp9bf]

When I connect to a PBI dataset in PBIX. Does that bypass an existing RLS? So I can see all the data in PBIX although I am not allowed? (I would check it myself, but I am on-premise currently)

[@how2data703]

After getting my live dataset into Power BI Desktop, all of my get data related buttons are greyed out.. how can I get an excel spreadsheet into the model with a dataset that was shared with me?

[@adamcanfield9682]

It's currently in preview, so head over to Options and check the box "Direct Query for PBI Datasets and AS" under "Preview features".

[@bholu137]

Insane amazing!

[@TheColjackoneal]

What does this look like when publishing to the service? Does it create a new model?

[@chayakiraneng]

Yeah.. it created a new dataset for me. Even if I just add a single calculated column to the Enterprise data model

[@GuyInACube]

Yup. When you use this, it is creating a local dataset with references to the other models in "DirectQuery" mode. Think of it as a wrapping model. So, you will see a dataset that represents that object.

[@UTUBE787878]

Hi all, I was pretty excited by the main new feature "DirectQuery for Power BI datasets" BUT when I tried it I was able to pluginto a PBI dataset and then when I wanted to add another datasource, in my case a SAP BW message server, the Direct Query connection was nto available, only the Import mode :-( is this a current limitation ? will this be possible to ad a BW connection in DQ ? thanks for your help! Pierrick

[@mrkzmusic]

When will this feature be released?

[@1yyymmmddd]

Didn't get what is so cool about this. She is adding local Excel file to the Desktop. Wouldn't it be more useful to be able to add Excel file to the Service, then share it and get connected to it as to the shared dataset?

[@EduKusanagi]

Finally!!!!!!!!!!!

[@GuyInACube]

Agreed! 👊

[@krisvandamme9013]

... must try... soon... verry soon!!!

[@alejandropelaez3473]

This is so great!! I tried and had some issues. I have 3 data sets; two in one workspace and the third in a second workspace (all premium workspaces), created a report the 3 data sets, published it in none of the workspaces and it looks and works great. When I shared it, the users cannot see the visualization of the report. They can see other reports connected to each individual data set, but not for the combine data sets report, what am I missing? it works or me but not for the people I shared it with? please help

[@bakalula]

Tried it, can't connect, in PBI desktop it throws: "An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source." ....hm, logged in, should have every permission possible...hmmm...dunno...waiting on a detailed video from you guys :)

[@GuyInACube]

Not much to go on. Not sure why you would be hitting that error. Check the limitations called out in the announcement blog to see if maybe you are hitting something there.

[@JasonHurley]

So exciting! A little bummed it doesn't work for SSAS, but I guess we'll have to do a workaround by connecting a pbix to SSAS, publish it, then connect to that dataset? Is that realistic?

[@konstantinvolke4569]

I tried it and right now, it didn't work. I read on the blog that i'll work with the next major SQL Server Analysis Service release. I wonder when that will happen!?

[@GuyInACube]

Yeah not supported right now :(

[@surjantodjajapermana775]

Great Feature, I need it. But I have problem..... I am using PBI Desktop "Version: 2.88.721.0 64-bit (December 2020)". I think this is already the latest one, but after I connect to PBI datasets, the other data connections are greyed out. I cannot select excel, SQL, etc. What did I miss?

[@surjantodjajapermana775]

got it. Need to enable "DirectQuery for Power BI datasets and Analysis Services" in Preview features (under Options and Settings)

[@vaishnaviv3049]

I wish there wasn't any background music. Anyways, that's a great feature!

[@TheMwaters4]

woohoo! Exciting!

[@GuyInACube]

YES! Excited to see what folks do with this! 👊

[@PedroCabraldaCamara]

yesssssssssss!!!!!! Very gooooooood!

[@GouthamSudini]

I am using July 2021 version of Power BI desktop, I see that the data sources get disabled once I connect live to either a Power BI dataset or a tabular cube. Thoughts?

[@RKTECH1021]

Hi I am having same issue, I am using latest version of Power BI. I just can't see the way to connect second datasets once I am connected to one. :)

[@RKTECH1021]

Hi Gautham, Yes, I had same issue, but now my data sources not grayed out. i am able to use this functionality. using September 2021 edition of Power BI desktop. once you enable preview feature, then it requires a restart of Power BI desktop. looks like previous version didn't give this restart warning, I don't remember. give a try.

[@GouthamSudini]

@@RKTECH1021 thank you! Yes, it did work now!!

[@ospino1112]

I'm not seeing this in Pbi

[@adamcanfield9682]

It's currently in preview, so head over to Options and check the box "Direct Query for PBI Datasets and AS" under "Preview features".

[@Marcel-dt5du]

I'm using the latest PBI Desktop and the preview features is toggled on. However, when connecting to a PBI dataset (only 1 table = push dataset) I do not have the option to add additional datasources or to switch to DC.

[@adamcanfield9682]

It's currently in preview, so head over to Options and check the box "Direct Query for PBI Datasets and AS" under "Preview features".

[@Marcel-dt5du]

@@adamcanfield9682 thx for reaching out. It is toggled on already.

[@aspence4053]

@@adamcanfield9682 Thank you for this. Mine was greyed out as well and this solved it.

[@mcnater]

Is there a limit to how many PBI Datsets I can pull into a report? At my org we have liets say 20 "Certified" datasets. Am I fine bringing in 10 of those into a single report? The chaining explanation isn't quite clear.

[@GuyInACube]

I don't know if there is a limit on how many you can pull in. Although, I would be careful with doing that. I can only imagine the performance issues you would hit when doing something like that having them all joined together. The chaining limitation is about nested datasets. So, if I created Dataset B that brings in Dataset A. Then I created Dataset C that brings in dataset B. I would not be able to go another layer deeper and create Dataset D that brings in Dataset C.

[@mcnater]

@@GuyInACube Got it. Appreciate the response.

[@noahhadro8213]

how does RLS work with this? I connected to a powerbi data set and an excel import. They can only see the visual using the excel import. All the other visuals are broken?

[@GuyInACube]

The doc calls out a limitation with RLS docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-services#considerations-and-limitations RLS rules will be applied on the source on which they are defined, but will not be applied to any other datasets in the model. RLS defined in the report will not be applied to remote sources, and RLS set on remote sources will not be applied to other data sources. I'm not sure why your visuals are broken specifically. If you have created relationships, I'd recommend avoiding the relationships and do the work from a DAX measure instead with TreatAs or something similar and see if that helps.

[@noahhadro8213]

​@@GuyInACube thanks so much for a reply. It is really strange. to test I created a report with 1 very basic visual. it is a table with two columns. "Date" from date table and a measure "Sum(Sales_Account[Price])" from my fact table. I tried to control the variables. 1) I have RLS applied to the model and publish to the service. I start another power bi desktop model and connect to the dataset like you would prior to the new direct query feature. Then I publish to web and everything works great and all users can see the data they are suppose to. I go back to the desktop and click "make changes to model". once it makes a local model I publish the exact same way to the service and then to the app (just like I did prior to clicking "Make changes to model"). The visual breaks for all users. The only people it works for are admins of the workspace? Thoughts?

[@noahhadro8213]

@@GuyInACube After further investigation I found out this. In "Manage Permissions" under the source power bi data set they have to have "BUILD" permissions to see the data in the other report that is using direct query. Is that how it should be? I don't want my end users to have "BUILD" permission. I only want them to have "APP" permission which from my understand is the same as "READ". Are you experiencing this?

[@snicket87]

Greaaaaaat!

[@MuffinTessee]

Somehow didn't know this was a thing for a month and a half.. how could I miss "the holy grail of BI"

[@bensycamore]

I've been clamoring for this for a LONG time!

[@noahhadro8213]

When it prompts me to create a local model. I get an error that says "The given key was not present in the dictionary.". Can you help?

[@GuyInACube]

hmmm may be something specific with the model you are trying to work with? Does it happen with every dataset you try to do this with, or just the one?

[@noahhadro8213]

@@GuyInACube thanks so much for the response. Yes it seems to happen with any dataset that is decently big (341,000 KB)

[@roberttyler2861]

@@noahhadro8213 Happens with mine too

[@roberttyler2861]

Just to add onto this. As GIAC suggests, I used another dataset and it worked!

[@pw6296]

I am also having this same error. I am using large models, with a combination of Dual and Imported tables. The data source is Snowflake. I did edit some tables in Tabular Editor which may have corrupt the model for this function. File sizes of around 200 MB. What are your specs? It would be great if someone is able to pinpoint what causes the issue!

[@Sibrulotte]

Scrolled through the comments to see if I was the only one that still had greyed out Data buttons :(

[@Volkswagenferper]

In our case, this simplifies a lot the way we handle data: we hav 5 departments, all with different data sources. Nos, instead of buidind a huge single data model we can word individually and join them. I do not know, and the video does not say, if this affects schedule updates or not. I take it does not, for if a gateway is configured for the sources, everything ought to work. right? anyone has tried?

[@shoaibahmedkhan9676]

Feel like crying :O

[@toptification]

👌🏽👌🏽👌🏽👌🏽

[@Phoenixspin]

Tessa needs to work on her "yoooo".