Power BI should be the backbone of reporting for all organizations. Helping to give decision-makers the right information they need, at the right time, to make good and effective decisions for their organizations. While exceedingly versatile and powerful, Power BI comes with a steep learning curve. It takes months and years to master this application and its many functionalities. It has many hidden features and unknown best practices which can stymie and frustrate new users. This series of articles is going to focus on the hidden secrets, capabilities, features, and functions of Microsoft Power BI.
So, you are ready to take a deeper dive into your data? Power BI is a dashboard business intelligence reporting tool. You can pull data from excel spreadsheets, cloud services, streaming data, and on-premise databases concurrently. Power BI is a robust tool, it takes time to develop Power BI reports. Power BI, like other tools in the Microsoft business intelligence suite, combines all of your data sources together into one place for quick and easy reporting and analysis. But the first step is getting your data into the program.
[/section] [section bg_color=”#1476ac” dark=”true”]This blog post will outline the first 5 hidden secrets – Which start with how to get your data into Power Bi:
[row][col span=”5″ span__sm=”12″]Data connectivity with Power Query
Import
Direct Query
Composite Mode
Manual Data
[/col] [col span=”7″ span__sm=”12″][ux_image id=”747″][/col][/row][/section] [section label=”Media Left” bg_color=”rgb(193, 193, 193)” bg_overlay=”rgba(255, 255, 255, 0.85)” padding=”60px”][row style=”large” v_align=”middle”][col span=”6″ span__sm=”12″]Data connectivity with Power Query
[ux_image id=”762″][/col] [col span=”6″ span__sm=”12″ align=”left”]Power Query allows a user to “hook up” their dashboard to a dynamic data source such as a spreadsheet, a database, or information from the web. Instead of exporting a report to Excel or copying and pasting data, Power Query dynamically retrieves that data from the data source. Lean more about Power Query at https://goo.gl/dh5r9y
[/col][/row]The operations necessary to retrieve that data are retained and can be used for future updates. In the future, all the user needs to do is refresh their workbook to fetch any new or changed records. Power Query is a powerful tool that can be used to connect your Power BI dashboard to a wide variety of data sources.
[/section] [section label=”Dark section with box right” bg_color=”#1476ac” padding=”59px” height=”300px” border=”1px 0px 0px 0px” border_color=”rgb(235, 235, 235)”]Import Function
[row style=”large” v_align=”middle” h_align=”center”][col span=”6″ span__sm=”12″ align=”center” color=”light”]The Import function allows PowerBI users to import data from many different types of files directly into Power BI. This includes Excel, JSON, and CSV files as well as text files and databases like SQL Server or Oracle.
[/col] [col span=”6″ span__sm=”12″ padding=”15px 15px 15px 15px” align=”left” bg_color=”rgb(255, 255, 255)” animate=”flipInY” depth=”2″ depth_hover=”5″][ux_image id=”761″][/col][/row] [ux_text text_color=”rgb(255, 253, 253)”]The import method will copy your data verbatim into your Power BI report and update it periodically. PowerBI users have the option to update these files on an hourly, daily, or weekly basis. The Import Method is the most common method to get data into Microsoft Power BI.
The import method has several benefits. It generates query “templates” using metadata to define the model structure and runs commands in Power BI to create the query model and pull data from data sources. Size restrictions aren’t a factor, and there is no need for a data refresh. The ability to utilize the Automatic Page Refresh is another advantage of importing
The disadvantage of the import approach is that the entire model must be loaded to memory before Power BI runs the query. It also requires more capacity resources, especially as the model grows. The data is only as current as of the most recent refresh; a refresh schedule is also required. Running a complete refresh removes all table data and re-loads from the data source.
[/ux_text][/section] [section bg_color=”rgb(240, 240, 240)” padding=”59px” height=”300px” border=”1px 0px 0px 0px” border_color=”rgb(235, 235, 235)”]Direct Query
[row style=”large” v_align=”middle” h_align=”center”][col span=”6″ span__sm=”12″ padding=”15px 15px 15px 15px” bg_color=”rgb(255, 255, 255)” depth=”2″ depth_hover=”5″][ux_image id=”817″][/col] [col span=”6″ span__sm=”12″ align=”center”]Direct Query allows Power BI to access data directly from your source without importing it first! Direct Query is a great feature that Power BI offers. The direct query method keeps the data at the source and updates it on-demand as needed. PowerBI will only import and store the metadata of the dataset you want to report on in Power Bi. Learn more about DirectQuery at https://bit.ly/3F4UZi9
[/col][/row]One of the reasons to use the Direct Query method is that Power BI doesn’t have to store a copy of your data. That will save PowerBI users the storage space required for Power Query imports. Another reason is that DirectQuery allows PowerBI users to access more data than Power Query does because it uses fewer resources, and there are no size restrictions on the original dataset. Does not require a data refresh and can use the Automatic Page Refresh.
There are drawbacks to each approach. Some of the drawbacks for Direct Query are that calculated tables are not supported, for example. Quick Insights functions aren’t available. DAX formulas may only be used on functions that may be transposed to native queries that the data source can read and understand. When multiple users query the same model at the same time, performance is reduced.
[/section] [section bg_color=”rgb(20, 118, 172)” dark=”true”]Composite Method
[row][col span=”6″ span__sm=”12″]The composite method mixes import and direct query methods giving high query performance of near real-time data. The Power BI Desktop will import the data into a local cache and then Direct Query against the cached data.
[/col] [col span=”6″ span__sm=”12″][ux_image id=”818″][/col][/row]It also allows the integration of multiple Direct Query data sources and storage modes for each model table. This method supports DAX calculated tables as well. Learn more at https://bit.ly/3dXIucj
Some disadvantages of Microsoft Power BI’s Composite mode are that Power BI stores all the data in memory, which can take up a lot of space. Power BI only allows one Composite mode per model, and it is not possible to use Power Query to transform the source data before loading it into Power BI.
[/section] [section label=”Media Left Large” bg_color=”rgb(240, 240, 240)” padding=”0px”][row style=”collapse” width=”full-width” v_align=”middle”][col span=”6″ span__sm=”12″][ux_image id=”819″][/col] [col span=”6″ span__sm=”12″ padding=”5% 5% 5% 5%” align=”left” max_width=”520px”]Manual Data Entry
Manual Data is an old-school way to get your data into Power BI. This method requires you to copy and paste your data from one application to another. It is not recommended for importing large datasets, but it can be useful. Instead of querying and loading values into your new table’s columns from a data source, you create a Data Analysis Expressions (DAX) formula to define the table’s values.
[/col][/row]You create calculated tables by using the New table feature in Report View or Data View of Power BI Desktop. You can then join this table to other tables in your dataset. This is a useful method for static measures, KPIs, goals, etc. where you need a static value for the calculation. Learn more about calculated tables at https://bit.ly/3p7GQv4.
[/section] [section label=”Media Top” bg=”820″ bg_color=”rgb(193, 193, 193)” bg_overlay=”rgba(255, 255, 255, 0.85)” padding=”0px”][row style=”large” h_align=”center”][col][ux_image id=”820″][/col] [col span=”9″ span__sm=”12″ align=”center”]As Power BI continues to evolve, the methods of data importing will continue to change. Power Query and PowerBI’s Data Flows are very useful for handling large datasets with a variety of different types of data sources. It is well worth taking the time to understand their differences and when each should be used! For more in-depth dives into the world of Power BI for Financial professionals, take one of our courses!
[/col][/row][/section] [section bg_color=”#1476ac” dark=”true”][title style=”center” text=”Want to Learn More? Check Out These Courses” tag_name=”h4″ margin_bottom=”15px”][row][col span=”4″ span__sm=”12″][ux_image_box img=”821″ link=”https://www.cpetoday.com/course/4wc8/k2s-power-bi-advanced-reporting” target=”_blank” text_bg=”rgba(0, 0, 0, 0)”][ux_text text_color=”#1476ac”]K2’S Power BI – Advanced Reporting
[/ux_text] [ux_text text_align=”center” text_color=”rgb(224, 93, 0)”]2 Credits | Technology
Lawrence McClelland
$89
[/ux_text][/ux_image_box][/col]
[col span=”4″ span__sm=”12″][ux_image_box img=”822″ link=”https://www.cpetoday.com/course/pbi1/power-bi-for-financial-professionals-part-1?autoplay=true” target=”_blank” text_bg=”rgba(0, 0, 0, 0)”][ux_text text_color=”#1476ac”]Power BI For Financial Professionals – Part 1
[/ux_text] [ux_text text_align=”center” text_color=”rgb(224, 93, 0)”]
1 Credit | Technology
Steve yoss
$19
[/ux_text][/ux_image_box][/col]
[col span=”4″ span__sm=”12″][ux_image_box img=”823″ image_height=”56.25%” link=”https://www.cpetoday.com/course/pbi2/power-bi-for-financial-professionals-part-2?autoplay=true” text_bg=”rgba(0, 0, 0, 0)”][ux_text text_color=”#1476ac”]Power BI For Financial Professionals – Part 2
[/ux_text] [ux_text text_align=”center” text_color=”rgb(224, 93, 0)”]
