What are Dataflows?
According to the Microsoft website: “A dataflow is a collection of tables that are created and managed in workspaces in the Power BI service. A table is a set of columns that are used to store data, much like a table within a database.” Dataflows capable of taking care of some of the ETL. You basically get the same Query editor as if you were creating a regular dataset. On thing to take not is that you cannot create relationships in de Dataflow itself, you should take care of this in the Dataset. Since one of the later releases it is possible to use multiple datasets in your report, so Dataflows and Datasets have some overlapping functionality.
When to use Dataflows?
Create reusable transformation logic that can be shared by many datasets.
Create a single source of the truth by forcing analysts to connect to the dataflows.
If you want to work with large data volumes and perform ETL at scale, dataflows with Power BI Premium scales more efficiently and gives you more flexibility.
Prevent analysts from having direct access to the underlying data source. Since report creators can build on top of dataflows, it may be more convenient for you to allow access to underlying data sources only to a few individuals, and then provide access to the dataflows for analysts to build on top of.
Use PowerBI Desktop
For some reason PowerBI Desktop is way quicker, so just start your query in PBI desktop. Once you’re happy copy the code from the advanced editor and paste in your dataflow. You will most likely receive some error’s due to syntax differences. Luckily the online Query Editor will suggest the changes to make your code run.
Syntax
The syntax between PowerBI Desktop and Dataflows is slightly different. For example where PowerBI generates “Promoted Headers”, Dataflow will do this as such “Promoted headers”. Not a huge problem however the code is case sensitive, so you must manually correct the error. SO keep that in mind if you copy bits of code from the Query Editor in PBI desktop to the Query Editor in Dataflow.
Replacing errors
Another difference between Power Query in Dataflows as opposed to Query Editorin the Desktop is that it replaces alle data errors with null. In my opinion a benefit to keep your existing dashboards from failing. However, if you receive such an error, you will not be able to tell directly why there is no data in your report. This is an extra step the query editor creates. You could delete this step, however next time you start up the Query Editor the step will be there again.