Hello Data Enthusiasts! Sometimes, it’s important to check how good your data is in your tables. Even though you usually do this when getting the data ready to find out if there any outliers or issues with your data for correction, it’s handy to have a report for regular users too. That way, if they […]
Enhanced Data Profiling in Power Query: GUI and Table.Profile Function with 2nd Argument Read More »
Have you ever encountered an issue where your calculations in Excel and Power Query don’t match up due to the way rounding is handled? Rounding is a crucial aspect of financial calculations, and inconsistent results between Excel and Power Query can lead to costly mistakes. Let’s take a look at an example. Say you have
Excel vs Power Query: The Rounding Dilemma Read More »
In this video, I share my solution to a Power query challenge posted on the Excel BI Linkedin page on 17 Oct 2022. It may seem that the question is simple, but there are some nuances that must be taken into account in order to come up with a robust solution. By combining a variety
Power Query Challenge & Solution | No of Wins by Soccer Teams Read More »
If a column in your table has duplicate values, Power BI won’t allow you to sort it by another column, unless both columns have the same granularity. To set a different column to sort by in Power BI, both columns need to be at the same level of granularity. For example, you need a column
Power BI Tip: How to Sort a Column with Duplicates by Another Column Read More »
Here is a Power Query ETL workaround that I implemented to transform multiple columns using a single step based on another column in the same table. I had to deal with this challenge in my most recent Power BI project on Financial Reporting. When using just the interface (GUI), it may take more steps and
Transform Multiple Columns Based On Other Columns In Power Query With A Single Step Read More »
In this article, I will be sharing a technique using List.Accumulate function to calculate the Exponential Moving Average (EMA) in Power Query. When it comes to recursive operations, the List.Accumulate is indispensable and becomes handy in scenarios of this nature. Read through the blog and download the attached PBIX file to learn more.I will be sharing a solution
Exponential Moving Average in Power Query (EMA) Read More »
I am sure people spend more time and effort cleaning and organizing their data when they need to perform data analysis in Excel, Power BI or any other tool. You do not always receive data in tabular and normalized formats for you to straightaway use the analysis tools like Pivot Tables, Filtering, Chatting and data
Pivot, Unpivot and Transpose Data with Power Query Read More »
Allocating amounts such as contracts, prepayment, revenue, cost, etc. across different date ranges is a common Excel challenge that can be solved in many ways. Let’s assume you have some rent contracts with start date, end date, and the amount which needs to be split into months based on the exact number of days in
Split Amount across Months Using Power Query in Excel and Power BI Read More »