Power Query offers the ability to clean and transform data with ease. Follow along with this Preppin' Data challenge to see just how intuitive it really is.
Let's get started!
Once you’ve imported your file into Power BI, click on Transform Data to open Power Query.
Before making any changes, it’s important to create a step as a marker. Power Query automatically records each transformation you apply, so having a clearly marked starting point makes it easier to trace back if you make a mistake, want to review what was done or need to hand your work over to someone else.
Splitting Columns
The next step is to transform the data using the Split Column by Delimiter function. You can customise the delimiter and under Advanced Options, choose where the split should begin or end. This allows for greater flexibility depending on the format of your data.
The result includes renamed columns and an extra split applied to one of the fields to separate the from and to flight destinations.
Replacing Values
Next, we’ll replace the values 1 and 0 with Yes and No.
One important thing to note: the data types must match when using the Replace Values function. You can only replace integers with integers, and text strings with other strings.
Duplicating and Filtering Tables
The final step is to duplicate the table, creating two versions. In each table, apply a filter to the flow card field, one to show passengers with a flow card and the other for those without.
That’s the Preppin’ Data challenge complete! As you can see, once you get used to how Power Query operates, the process is straightforward and efficient.
Why not give it a try yourself?
Link to challenge : https://preppindata.blogspot.com/2024/01/2024-week-1-prep-airs-flow-card.html