Welcome back to this week’s edition of the Power BI
blog series. This week, we look at what Power BI dataflows are, and how
they may be created.
In Power BI Desktop,
we use Power Query to extract, transform and load the data needed for our
reports. In Power BI Service, dataflows
allow us to manage group of tables in a similar way to Power Query, but dataflows run on the cloud using Power Query Online and are independent of
the Power BI dataset or solution.
Similarly to Power Query in Power BI
Desktop, Dataflows is a powerful
tool that allows us to extract, transform and load data from various sources
into a single, unified data model in an easily accessible location. This data model can then be used across
multiple reports and dashboards within a Power BI workspace, making it a great
tool for data preparation and management.
Some of the benefits of using dataflows in
a workspace are:
- the data model
can be shared between reports, reducing repetitive processing - data management
within the workspace is improved - we may control
the way data sources are accessed - report refresh
times are improved - scheduled
automatic data refreshing, which keeps the data up to date without manual work.
Dataflows also allow
sharing of data models among multiple users within an organisation. This enables teams to work together more
efficiently, since they can access and use the same data model. If each team member has their own data model
to maintain, discrepancies may arise between the reports produced by different members.
To create a dataflow, we
need to launch Power BI Service.
Dataflows are not available in Power BI Desktop. We log in to Power BI Service and navigate to
(or create) the workspace that we are going to use the dataflow for. Note that dataflows cannot be created from
‘my-workspace’.
To create a new
dataflow, we click on the ‘+ New’ button on the workspace and select Dataflow.
We are then presented
with four [4] possible ways to start creating the new dataflow:
The methods are:
- define new tables
- linked tables from other dataflows
- import model
- attach a Common Data Model (CDM) folder.
Since the other
options are using existing dataflows as a basis, we will describe how to create
a dataflow by using ‘Define new tables’.
We click on the ‘Add
new tables’ from the ‘Define new Tables’ frame and choose the data source we
wish to connect to, such as Excel, SQL Server or SharePoint. Below are some examples of the data sources available
on Power BI Service:
Once we have chosen a source, we follow the
connection wizard to import the data into Power BI, and then click the ‘Next’
button to continue:
We are then taken to the Power Query Online editor where we
can edit and clean the data using
tools familiar to users of Power Query in Excel and Power BI Desktop. Some examples of transformations available include
removing columns, merging tables, adding calculated
columns, filtering, grouping or
pivoting. We can also create calculated
tables, which are tables that are generated from expressions or formulae.
In a similar way to the Power Query editor
in Power BI Desktop, we can click on the ‘Save & Close’ button when we have
completed our transformations. We are
prompted to save the dataflow and assign a Name. The optional Description can be used to give
other users an idea of what data the dataflow contains and its intended
purpose.
Having saved the dataflow
under the name ‘Types’, it appears in the workspace:
Next time, we will
look at how to use the dataflow we have created and how we would use it to
create a new dataflow.
Check back next week for more Power BI tips
and tricks!
Be the first to comment