Splitting columns
When working with data, particularly data extracted directly from source systems, there often comes a time when we will need to split columns to gain the desired dimensions for our analysis. This might be because the software or database of that source system might store that data in a particular format/encoding/arrangement. The most common example of this could be splitting a Date field in order to extract dimensions for [Day], [Month], and [Year].
In this example, we will connect and open the calendar.xlsx file. This Excel table includes one column of dates (as shown in Figure 4.8) and is to be used as a date table within Power BI. In this example, we might need to extract the individual date components for our analysis. Once connected, select Transform data to enter Power Query once again.
Figure 4.8 – The Date column within the date table in Power BI
In order to split the columns, we will use the prebuilt function for splitting...
