Model Data for Machine Learning in Power BI

In Chapter 3 of this book, you prepped FAA Wildlife Strike data for a Power BI dataset, built a relational dataset that will function as a foundation for analytics, and then configured basic settings in that dataset so that you could take a deep dive into the data and discover features for ML in Power BI.

As you begin discovering features in the data for your ML models, you will need a process for adding those features to queries that can be used for training and testing those models in Power BI. In this chapter, you will build out an analytic report in Power BI as you explore the dataset for features suitable for ML in Power BI. When features are discovered, you will create queries within Power Query that will eventually serve the purpose of training and testing your ML models.

Technical requirements

For this chapter, you will need the following:

• Power BI Desktop April 2023 or later (no licenses required)
• FAA Wildlife Strike data files from either the FAA website or the Packt GitHub site: https://github.com/PacktPublishing/Unleashing-Your-Data-with-Power-BI-Machine-Learning-and-OpenAI

Choosing features via data exploration

Your project is to be implemented completely within Power BI, without using external tools. Power BI ML is a software as a service (SaaS) tool that does not require the setup of an infrastructure or advanced coding skills. Traditionally, most ML projects are implemented using highly specialized tools that require strong coding skills with languages such as R and Python. By implementing the entire project in Power BI, you will be able to complete it in a short timeline, build all of the components with SaaS tools and minimal coding, and then manage deployment, scalability, and future changes using a single suite of tools.

The data architecture techniques in this chapter are tailored to analysts and business intelligence developers, and the process will be a great way to learn the basics of finding and modeling features for ML. Experienced ML architects who are fluent in R or Python might handle the process differently, but you need to proceed...

Creating flattened tables in Power Query for ML in 
Power BI

Now that you’ve done some analysis and discovered features that you’d like to include for your first round of ML models, you return to Power Query and get to work. You review your notes from the previous sections of this chapter and begin with the Predict Damage table in the ML Queries group.

Modifying the Predict Damage table in Power Query

Since a row will represent an individual event that is predicted, you do not need to do any groupings with the data. You will be selecting columns and filtering down the rows to a set of data that is better suited to the task:

1. Highlight the Predict Damage table in the ML Queries group.
2. The aircraft class code for airplane is A, so filter the Aircraft Class Code column to A.
3. Filter the Incident Date column to be on or after 1/1/2014.
4. On the Power Query ribbon, select Home | Manage Columns | Choose Columns and keep the Size and Indicated Damage...

Summary

This chapter began building the foundation of your ML adventure in Power BI. You created a group of queries in Power Query that will be the basis for training and testing your binary, general classification, and regression ML models. You began building out an analytical report while discovering features in the data that might be valuable for predictive analytics in ML. Finally, you added those features to new queries in Power Query. You’re ready to add new features to your queries that will attempt to predict whether damage occurred, the size of the wildlife involved in a strike, and the height of wildlife strikes.

In the next chapter, you will dive deeper into the Power BI dataset to discover new predictive features. You’ll leverage some of the Power BI AI capabilities to uncover new insights and potentially new features for use with ML. Finally, you will add your new findings to the queries that will be used to train and test your ML models in Power BI.

...