Power BI is a powerful business analytics service provided by Microsoft. It empowers users to visualize and analyze data from various sources, transforming raw data into meaningful insights. With its user-friendly interface and robust features, Power BI has gained immense popularity among businesses of all sizes. It offers a comprehensive suite of tools for data preparation, modeling, and visualization, enabling users to create interactive dashboards and reports.
Power BI offers numerous advantages for organizations. By providing a centralized platform for data analysis, it improves decision-making processes and enhances operational efficiency. It enables users to explore data, identify trends, and uncover hidden insights. Moreover, Power BI’s integration with other Microsoft products, such as Excel and Power Apps, further enhances its capabilities and streamlines workflows. Organizations leveraging Power BI can gain a competitive edge and drive data-driven innovation.
What is a Power Query?
Power Query, a powerful data integration and transformation tool, empowers users to connect to diverse data sources, and clean, transform, and shape data into a desired format. Using intuitive drag-and-drop interfaces and a rich set of functions, you can easily extract, transform, and load (ETL) data from various sources, including spreadsheets, databases, and web APIs. Power Query enables you to automate complex data preparation tasks, standardize data formats, and create powerful data models.
So, say you encounter a situation where you need to add time durations or average out time values in Power BI. Doing it using the usual 24-hour hh:mm format can be very confusing and is a great way to make mistakes in such calculations. Manually adding everything is also not always feasible, especially when there are multiple time readings involved. Then what do you do?
The easy way to do it is to convert the timings from the conventional time format (hh:mm) to a decimal hour format. This would make the times easy to add, subtract, or average out.
Now, if your first thought was to use the usual standard transformation in Power Query, it is not that simple. When adding or averaging decimal time values, the totals could easily go upwards of 24 hours. This makes using standard transformation not a feasible option.
If you thought about treating the value as a „Time“ or „Duration“ data type, then that is also not a feasible option because it will lead to errors and unusable results.
Then, how can this be accomplished?
Treating TIME as TEXT
The solution to this problem is to treat the time values as text. Mark off a colon to separate the hh and the mm. The minutes are then converted to a fraction of the hours and then added to the hour’s value.
So, here is what you can do.
Create a custom column using the Power Query. The below code lines should be helpful to do the same.
- Number.From(Text.Split([TimeDuration], “:”){0}) +
- (Number.From(Text.Split([TimeDuration], “:”){1}) / 60)
This code does four things to make this conversion possible.
First, the Text.Split ([TextDuration], ,,:”) function will split the text value of the time at the point where the colon is entered in the text. The split would result in two parts – {0} for the hours and {1} for the minutes.
Second, the Number.From(…) will convert the extracted values to simple numeric values.
Third, the minutes would be converted to fractional hours by dividing the number by 60.
Fourth, the numeric hour value and the fractional hour value would be added and a total number of hours would be produced in the decimal format.
Converting time values to decimal format provides a convenient and efficient way to perform mathematical operations, such as addition and averaging, within Power BI. By representing time as a decimal value, you can treat it as a numerical quantity, enabling straightforward calculations.
Say you have a list of time stamps of the punch-in and punch-out times for all the employees of a company and you want to calculate the total manhours invested by the employees in a calendar month, converting the values using the method described above could be helpful.
Another use case is, say you are measuring the doubling time of a bacterial population and want to total the values of multiple trials for different bacterial cultures, again this method would be helpful.
Yet another use case could be to analyze the time spent by different users on a particular application or platform on different days of the week, or segment the data by demographics and analyze it. Here also, converting the conventional time format to decimal hours would prove to be useful.
There are many such use cases where this method could be useful for data analysts and Power BI users. This solution is especially valuable when you are working in Power Query and need time values in a format suitable for further analysis or calculations.
To learn such tips & tricks and sharpen your Power BI skills enroll for Cognixia’s live instructor-led online Power BI certification course today.
Get Certified with Cognixia’s Power BI Online Training
Cognixia’s Analyzing Data with Power BI training course will introduce you to the principles of data analysis. It provides users with data warehousing tools to help organize data, data exploration, and interactive dashboards.
Power BI is now one of the world’s largest and fastest-growing business intelligence clouds. Microsoft was named a leader in the Gartner Magic Quadrant for analytics and business intelligence solutions for the 14th year in a row.
Cognixia’s Power BI certification course looks at the many methodologies and best practices for modeling, analyzing, and visualizing data using Power BI that follow business and technical needs.
This online Power BI training and certification course covers:
- How to ingest, clean, and transform data
- Modeling data for performance & scalability
- Designing and creating reports for data analysis
- Applying and performing advanced report analytics
- Managing and sharing report assets
- Creating paginated reports in Power BI
The Power BI course is highly recommended for data analysts, BI developers, SQL developers, technology engineers, application architects, software engineers, data visualizers, data translators, application designers, and software development specialists. Power BI is also a very useful tool outside the IT functions and has great business value in non-IT functions.
