One of the interesting calculation challenges in BI systems is a segmentation or grouping on a numeric value, based on an occurrence of another value. As an example; You might want to know How many customers ordered once, twice, three times or more. The challenge is that the data in the transactional table is not Read more about Customers Grouped by Count of Their Orders – Static Segmentation in Power BI[…]
Merging two tables in Power Query is one of the most useful transformations to combine data from different datasets, and also create a flatten data structure. However, if you use a text field for merge, then you might see some unexpected results. Here in this post, I share some important considerations if you are using Read more about Be Careful When Merging on Text Fields in Power BI Using Power Query[…]
Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that Read more about Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically[…]
In the last Post, I will explain how to analyze a JSON file that has been generated in the Sentiment Analysis process . some explanation, this is a JSON file that contains the sentiment analysis for the comments one traveler put on the hotel website as below The suite was awesome. We did not have Read more about Analyse the JSON File with Power Query[…]
In the previous part of the currency exchange rate conversion, I provided a function script that you can use to get live rates using a free API. In that part, you learned how you can create a table in dataflow using live rates. In this part, I will show you how you can use the Read more about Currency Exchange Rate Conversion in Power BI with Live Rates – Part 2[…]
If you ever need to convert amounts from one currency to another, you face the challenge that currency rates are not constant. They change every day. You either need to keep an up-to-date table of all new currency rates and use that in your Power BI solution, or use a function that gets the rate Read more about Currency Exchange Rate Converter Power BI Function – Part 1[…]
I have previously written about how to sort a column by another column, and I used Month Names as an example. However, still, many are unaware that the same technique with slight modifications can be applied to any other columns. You can have a text column in your slicer (product category for example), and sort Read more about Sort a Column with a Custom Order in Power BI[…]
If you use SQL Server Analysis Services as a data source for Power BI, you have two types of connection; Live Connection or Import Data. If you use Import Data, you can select columns and measures to be imported into Power BI. However, what if you want to add more columns to it, or edit Read more about Add or Edit Columns When Importing Data from Analysis Services into Power BI[…]
I have written multiple blog posts about how to create a date dimension using Power Query, however, the purpose of those blog posts was to teach you how to do it yourself, learn the process and also build the date dimension that you can use. Sometimes, however, you just want to create a date dimension Read more about All in One: Script to Create Date Dimension in Power BI using Power Query[…]
I wrote a blog article yesterday about the Age calculation in Power Query, and how easy it is to calculate things. I mentioned in the blog that it won’t be precisely correct for scenarios that include leap years. However, I got some concerns that people are not still using it because they think this is Read more about Age in Years Calculation that works for Leap Year in Power BI using Power Query[…]