In the previous video and blog post, we considered how to use FILTERXML combined with arrays of functions to remove line breaks. In this tutorial, we shall learn how to perform the same operation using the Power Query. Let’s get started.
Video
Do take time to watch the tutorial video and subscribe to the YouTube Channel
Sample Data
In the caption below, we have the sample data that contains line breaks. The dataset is already formatted as an Excel Table using CTRL + T
Load to Power Query
Next, we need to get the data into the Power Query Editor. To do that,
- Right-click on any cell of the data
- Select Get Data From Sheet. Note, From Sheet is a new connector in latest build of Beta Channel of M365. You might see From Table/Range in your version which does the same job!
or
- Go to Get & Transform Data group under the Data tab of the ribbon and select From Sheet
From the caption below, the data is loaded to the PQ editor
Create List
Next, we are going to use the Text. Split M Function to get the data into list. To do that,
- In the General group of the Add Column tab, click on Custom Column
2. In the New Column Name box, provide Custom1 (not compulsory)
3. In the Custom Column Formula box, execute the M Code: Text.Split([Date],”#(lf)”). Note, the lf means Line Feed which represent the separator or delimiter. The above M Code means, we want insert line sheet to the Date column
- Click OK.
- Repeat the steps 1- 3 above to split the other columns – Payment Method, Brand, Customer Category, Units, Price respectively
Remove Columns
After we have encapsulated the data in list, we need to remove all other columns except the columns that contains the list.
- To remove columns, Select Date column, hold down the SHIFT key and select Price column
- Right-click and Select Remove Columns
Create Table
Next, we need to use the Table.FromColumns M Function to handle the list. To do that,
- In the General group of the Add Column tab, click on Custom Column
2. In the New Column Name box, provide Custom or any name preferred
3. In the Custom Column Formula box, execute the M Code: Table.FromColumns({[Custom1],[Custom2],[Custom3],[Custom4],[Custom5],[Custom6]})
Next, we need to remove all the list columns because we do not need them again.
- Select all the list columns
- Right-click and select Remove Columns
- Finally, click on the Tables double upward pointed arrows
- Uncheck use original column name as prefix as seen below
- Click OK
The data is now in proper shape useful for consumption or further analysis
Load to Excel Table
- To load to Excel Table, in the Home tab,
- Select Close & Load
- Select Close and Load To…
- In the Import Data wizard, click OK
Voila! The data is loaded to an Excel Table as seen below
See you in the next tutorial