Siirry pääsisältöön

Use Dataflows




Use dataflows to organize your work and to separate ETL from visualizations. Trust me, for I'm a stranger you found in a blog, this will change your report designing habits for good.

Your lineage should go from data source(s) to PBI report/dashboard/app like this:

    DS --> Dataflow -> Report

Datasources can be wherever they might be as long as you can connect to them from the PBI Desktop and PBI Service.

Dataflow will be designed in the Power Query Editor of  PBI Desktop and in the Model and Data views of PBI Desktop and published in PBI Service.
Report will be designed in the Power Query Editor of PBI Desktop and in the Model, Data and Report views of PBI Desktop and published in PBI Service.

The above stunning graph in more verbose format:

  1. Connect to your data source(s).
  2. Get the raw'ish data.
  3. Do the thing in the Power Query. Do the cleaning, transformations etc. Make the data so good that there's nothing for the usual end user to tinker.
  4. Disable all the loads except for the ones you wish to be seen by the dataflow users.
  5. Create a data model using the Data view of PBI Desktop. Make it nice. Like nice.
  6. If there's common calculations or measures to be done, do them. Use PQ or DAX, depending on the needs and your skill level.
  7. Keep away from the Report view. Hush. There's no need to go there. Forbidden. You remember Adam and Eve? This is your apple. Don't touch it or you'll be banished from the garden of low hanging fruits called clean dataflow.
  8. Publish your dataflow to PBI Service. Give it a descriptive yet good, sturdy name. Have a small party of 5 people with non alcoholic drinks and light snacks. Be considerate and take different allergies into account. Being a vegan is not an allergy, so you can freely not invite those kind of people.
  9. Design your report in PBI Desktop. Use the freshly created dataflow as a data source. Take some time to cherish the fact that there's no need to tinker with data as usually. Just hop on to designing the most fabulous report ever done in the history of fabulous reports. No more twiddling with text inputs in date fields! No more fiddling with data models! Just go on and create. Be the Bob Ross of PBI. There are no mistakes, no happy accidents, just pure enjoyment of clean, fresh data.

  10. Publish your report.
Bonus step: If it turns out (as it should) that the dataflow is pure gold and source of the ultimate truth, promote it or even certify it, if your tenant allows it.

Tunnisteet:

Kommentit

Lähetä kommentti

Tämän blogin suosituimmat tekstit

Understanding Power BI Context: What You Need to Know

It's crucial to comprehend the concept of context in order to use Power BI efficiently. In this article, I'll examine what context in Power BI is and how it can be utilized to produce reports that are more useful. What Are the Power BI Contexts? Row context and filter context are the two different types of context available in Power BI. These contexts specify how a report's data is filtered and computed. What Distinguishes Row Context from Filter Context? Whereas row context applies filters to specific data rows, filter context applies filters to the entire report. In other words, row context filters the data after it has been calculated, whereas filter context filters the data before it. What Does Power BI's Context Filter Mean? A filter type known as a context filter modifies the report's filter context. As a result, you can filter report data without altering the underlying data. You could add a context filter to a report that displays sales by region to only dis...
Lähetä kommentti
Lue lisää

Get Forms results directly to Power BI

Old fashioned form analyzing requires extra protection. Forms is a handy tool to collect user input for various purposes. You can check the results directly from Forms service online, but for more in depth analytics the results can be downloaded in an Excel file. That means that the results can be interpreted and refined in Power BI with ease. So, let's get on to it. Create a form with Forms If you haven't had the chance to create forms with Forms before, there's no need to be afraid. It's really straight forward, so I won't go through the process here. Really, there's nothing to it. There's plenty of tutorials out there. For example check out Kevin's excellent video on the subject:  How to use Microsoft Forms Get form id The part we are interested in your newly created questionnaire is the form id . Open you form if it isn't open already in editor or in preview. Check the URL from the browser and look for part starting with  FormId= Starting aft...
Lähetä kommentti
Lue lisää

How to read XLS in to Power BI

Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider is required to connect to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987 It looks like you tried to open a XLS file with Power BI. Yeah, no. XLS is forbidden territory, you can't just walk in there. You have to be sneaky. Here's the trick: Source.xls --> Excel-file located in OneDrive - -> Power BI Did you see it? It's right there, in between. Let's zoom in: XLSX-file located in OneDrive (or any other cloud storage for that matter) that imports the XLS file using Power Query It's really that simple. Locate the XLS file you wish to use. Import it into a cloud stored XLSX format Excel-file. Use desktop Excel to create the connection with Power Query. Read the XLS as Web.Contents and the...
Lähetä kommentti
Lue lisää