Migrating Self-Service Datasets to Dataflows in Power BI

Is there a simple process for converting your existing datasets into a dataflow? The answer is yes, and I’ll share some tips here for doing this in the Power BI Service without having to do a lot of rework.

Why would I want to do this, you may ask? It makes sense in a few scenarios.

Scenario 1: Converting Existing Datasets to Dataflows

• You’ve been in a self-service model for a while. Now your organization is ready to take your BI initiatives to the next level by cultivating a set of highly reusable data into a single workspace for enterprise use.
• Let’s say Scott from the fleet inventory team spent 6 months crafting a great power query that presents the vehicles and their availability dates.
• Now we want to take that dataset that is embedded within his Power BI workspace and turn it into a dataflow that can be used by the entire organization.
• Surely, we don’t want to start from scratch by going into the dataflow and rebuild Scott’s steps. This would waste a lot of time and we’d probably make mistakes. So, after this whole process, our datasets don’t match, and we spend tons more time trying to figure out what we did wrong.
• What we need is a way to copy and paste the work that Scott has already done.

Scenario 2: Preferring the Power BI Desktop Experience

• You’re starting a new BI initiative and you know you’re targeting a dataflow for your final delivery of the data to the end user.
• At this point you don’t know exactly where your data is going to come from.
• You’ll need to do some prototyping, experimenting with pulling data from certain files or databases. I will often pull from different sources and play with them a bit before I figure out what the source of the data will be.
• Plus, I want to be able to do this experimenting without having to go in and configure my gateway every time I want to create a new connect to a data source.
• With Power BI, you have the flexibility to connect to different data sources throughout your enterprise.

Scenario 3: Dataflows from SQL Statement

• If you’ve tried to build a dataflow and use the SQL Server connection, and then try to specify a custom query, you can’t do it. Well, you can but you have to go through the process I’m going to show you. Not the best way, but I’m not judging, I’ve done it myself.
• The Wizard doesn’t make the SQL statement available for you to populate whenever you’re offering the dataflow.

Now let me walk you through my process. There’s one thing you must understand about dataflows and a requirement for the data gateway. The Power BI service needs to have connectivity to the data source for your dataflow.

If that dataflow source is a SQL Server that exists in an on-premise data center, you’ll need to have your gateway configured so that the Power BI service has a way to get connected to the database. There are many resources out there that can show you how to do this.

At this point, I suggest you watch my video below as I’ll walk you through a demo of the process for converting your existing datasets into a dataflow. It will be easier for you to see it done then to explain the process.

For help in incorporating dataflows or Power BI in general into your organization, we’re here to help. Our data and Power BI experts can help you in any stage, from knowledge to roadmaps for success to implementation. Contact us or click the link below.