There are three ways that you can work with data in Power BI Desktop. First of all, the most commonly-used method is to import data into a dataset. This involves copying data from your data sources into Power BI’s own internal database. This gives you the fastest possible query performance and the ability to use all of Power BI’s features but it also has some downsides too: refreshing data can be slow, you have to refresh data regularly so that it stays up-to-date, and there are limits on the amount of data you can import into a dataset.
Secondly you can create a live connection to SQL Server Analysis Services. The benefits of doing this are that you can work with much larger data volumes, refresh data much more frequently, and have a single place to store all of the data for your reports. However this centralisation can be too inflexible for some users, who may need to model data in different ways, and of course buying and maintaining an instance of Analysis Services is expensive and time-consuming. Azure Analysis Services, a Platform-as-a-Service version of Analysis Services, is worth considering.
Finally you can use DirectQuery mode. DirectQuery is only available for some data sources, such as relational databases, and in this mode a dataset is created inside Power BI but it stores no data: whenever Power BI queries the dataset, the dataset in turn requests data from the original data source - for example by generating SQL queries against a relational database. The good thing about DirectQuery is that the data in your reports is always up-to-date, but it also tends to give the worst report performance and some Power BI features are not available when you use it.
In Power Query/Get & Transform in Excel you can only import data into the Excel Data Model, the equivalent of a Power BI dataset. Excel also has the ability to create the equivalent of live connections to Analysis Services, but DirectQuery is not available in Excel. It is possible to connect to some data sources from the Power BI service in a browser too.
