On Power BI Desktop, you don’t even have a choice – the only route to connect to data is via the “Get Data/Power Query” interface. Which is A-Okay with me. Even with Excel, I now connect to ANY data using Power Query.
Use Power Query to fill all your Get Data needs
Yes, ANY data. Even if I could connect using Power Pivot to those data sources and did not need any transformation – I still always use Power Query.
Power Query to get data, Power Pivot to model data. Avi then gives a few examples of scenarios, explaining where each fits in.