User Question: “How can I join two tables in Power Query based on matching values in two different columns?” Joining tables based on matching values in Power Query Data Preparation We will use a data model that includes the following tables: Step-by-step instructions Note that you can join two or multiple tables according to more … Read more
Here’s a question from a colleague: “How can I create a rolling 12-month average measure to calculate employee turnover rate using Power BI?” Rolling average measures in Power BI Data Model Let’s start by assuming the following data model:Table: EmployeeTurnoverColumns: DAX for rolling measure in Power BI Explanation The DATESINPERIOD function creates a table of … Read more
Here’s a question from a colleague:In our HR department, we often need to report based on our company’s fiscal year, which runs from April 1st to March 31st. How can I create a custom calendar table in Power BI to support our fiscal year reporting? Setup a fiscal calendar table in Power BI Creating a … Read more
Power Query imports date values as plain text more often than expected, especially from CSV files, web sources, and databases with inconsistent schemas. Text-formatted dates break time intelligence calculations, prevent chronological sorting, and block date-based filters in both Power BI and Excel. Converting these text columns to proper date values requires the right combination of … Read more
Here’s a question from David: Is there a Power BI function that allows to create a table showing specific values for a date range? Let’s assume that i am interested to create a dummy table showing sales figures for all days in July 2023. Any ideas on how to accomplish that. Add a column based … Read more
Here’s a question from Thomas: Sometimes, while building a model in Power BI Desktop, i would like to create a simple table containing random sales values for a specific date. Any ideas how to go about doing that? Thanks for the question, please find the detailed step-by-step below: #1 – Define a dates column To … Read more
Here’s a question from Marla: I have imported a table from Excel. When i bring it up in Power BI , dates are displayed in long form when i actually need the traditional mm/dd/yyyy format – basically get rid of the time values. Transform Date/Time to short dates in Power BI Proceed as following to … Read more
Problem Statement You have imported a dataset from Excel or an SQL database. The dataset has columns containing numeric data which data type is text. Before going ahead and use those columns in your Data Model you need to convert them to decimal numbers (whole or fractional). Convert string column to numbers in Power Query … Read more
Here’s a question from Judy: I have a large data set for which i create a calculated column that contains the division of two Power BI columns and shows up as a percentage . When i use the calculated column in a dashboard, the percentages gets aggregated as a sum. Instead of calculating row by … Read more
Here’s a question from Jason: I have a dataset containing sales orders info. I need to group data from different columns to generate a small dashboard. I understand that i am able to aggregate, then calculate the sum and average values per group using the Transform tab or using code. Can you provide a simple … Read more