Why DAX and Power Query are the Heart of Power BI ?

In Power BI, two fundamental building blocks power data transformation, modeling, and visualization—DAX (Data Analysis Expressions) and Power Query. These two pillars are the backbone of a solid data analytics solution, allowing users to manipulate, organize, and analyze data with accuracy.

For a seasoned Power BI developer, DAX and Power Query mastery is not only a benefit—it's essential. Although Power BI has an easy-to-use drag-and-drop interface, real-world business challenges tend to necessitate sophisticated calculations, data model optimization, and streamlined data transformation. This is where DAX and Power Query come into play, unleashing advanced capabilities that extend beyond standard reporting.

DAX enables developers to develop dynamic measures, aggregations, and time intelligence calculations, delivering greater business insights.

Power Query facilitates efficient data extraction, transformation, and loading (ETL), allowing raw data to be cleaned, structured, and optimized prior to analysis.

Here in this article, we are going to discover some particular DAX formulas and Power Query logic that can aid you in advancing your Power BI skill, rendering your reports not only good-looking but also high-performance, scalable, and informative.

Understanding the Data: The Significance of Data Transformation

Data in the real world is seldom available in a perfectly formatted form. It may have unwanted characters, inconsistent values, and a combination of text and numbers, which makes it hard to analyze as it is. Raw data must be cleaned and transformed to provide accuracy, efficiency, and useful insights.

This is where Power Query and DAX step in. Both play a distinct role in the process of data transformation:

Power Query assists in cleansing and preparing data so that only pertinent and properly structured data gets loaded into the model.

DAX enables dynamic calculation and transformation on the existing data in the model, thereby making it interactive and report-ready.

A well-organized dataset enhances performance, accuracy, and decision-making. Without proper data transformation, reports can generate inaccurate results, which can result in the wrong business decisions.

Take a Look at the Data Below

Let's look at an example dataset first before we continue. Notice the presence of all sorts of characters including text, numbers, and special characters making it hard to analyze. Let's clean it up and extract only the numbers using DAX and Power Query.

Let's solve this challenge using DAX.

_Get_Numbers_ = 
 
VAR Input_Column  = MAX(Sheet1[Amount]) 
VAR Result =
    CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1, LEN(Input_Column)), 
                "Char", MID(Input_Column, [Value], 1)
            ),
            MID(Input_Column, [Value], 1) IN {"0","1","2","3","4","5","6","7","8","9"}
        ),
        [Char], ""
    )

VAR Amt_Int_Conversion = CONVERT(Result, INTEGER)

RETURN Amt_Int_Conversion        

Let's See the Result

Is Everything Perfect?

At first glance, everything might seem fine.

? Data looks structured ? Only numbers are extracted from text ? Values appear in place

But wait... Not really!

Take a closer look at the Total in the table—it's inaccurate and doesn't give the correct result.

We need to fix this!

Let’s dive into DAX and make it right. ??

_Get_Numbers_ = 

SUMX(
    Sheet1,
    VAR Input_Column  = Sheet1[Amount]
    VAR Result =
        CONCATENATEX(
            FILTER(
                ADDCOLUMNS(
                    GENERATESERIES(1, LEN(Input_Column)), 
                    "Char", MID(Input_Column, [Value], 1)
                ),
                MID(Input_Column, [Value], 1) IN {"0","1","2","3","4","5","6","7","8","9"}
            ),
            [Char], ""
        )
    VAR Amt_Int_Conversion = CONVERT(Result, INTEGER)
    RETURN Amt_Int_Conversion
)
        

Now let's go the Result's again

? Hopefully, we have now achieved the expected total! ????

Now, Let's Move to Session 2! ??

We've successfully tackled the problem using DAX, but our journey isn't over yet. Now, let's dive into Session 2, where we'll solve the same problem using Power Query.

Power Query vs. DAX – Which One to Use?

Both Power Query and DAX are powerful tools in Power BI, but they serve different purposes. Understanding when to use each can significantly improve your data transformation process and report performance.

Power Query Solution

Number.From(
    Text.Combine(
        List.Select(
            Text.ToList(Text.From([Amount])), 
            each Value.Is(Value.FromText(_), type number)
        )
    )
)        

Let's See the Result Now

? Hopefully, we have now achieved the Same Result in Power Query ????

Conclusion

Both DAX and Power Query are essential tools in Power BI, each serving a unique purpose in data transformation and analysis. DAX is powerful for dynamic calculations and aggregations at the report level, while Power Query excels at data extraction, cleaning, and shaping before loading into the model.

As a Power BI Developer, being proficient in both ensures flexibility in solving data challenges efficiently. By mastering DAX for advanced calculations and Power Query for robust data transformations, we can create optimized, scalable, and insightful reports.