In this article we will walk you through the steps required to split a column by no. of character using Power BI.
Step 1: “Load a Dataset”
If you want to load your dataset, simply click on the “Get Data” button in the data group on the “Home Tab”. once you click on that it will show a drop-down menu with multiple data sources, select “Excel workbook” from there and click on that.
Once, you click on that it brings another dialog box, where you can pull your data file from your local system, select your file and click on “Open” button located at the bottom right corner of the dialog box.
Once, you are done with that it will bring a navigator dialog box, from there you can select your sheet from the left hand side and after that simply click on the “Check Box” and select your “Sheet 1” sheet. once you select, than click on “Transform” button located at the bottom right corner of the dialog box for saving your changes.
Once, you click on the transform button, it will take you into the power query editor, from there you can split your column and do the necessary modifications into your dataset.
Step 2: “Select a Column & Split By Number of Characters”
If you want to select your column, click on the column “Header”, once you are done with that finally your column will be selected for splitting.
Once, you reach the power query editor after that, you need to select the column which you want to split, simply goto home tab and in the transform group click on the “Split Column” option. once you click on that it will show you a multiple splitting methods, you can click on the “By Number of Characters” in there.
Once, you click on that it will bring up a dialog box, from there you can you can type a number characters “5” and below this option you will see another option named as split, from there you can select “once, as far left as possible” and at the bottom of this option you will see another option named as advanced options, simply click on the drop-down, it will show you splitting type, click on “Column” radio button. once it all done you will click on the “OK” button located at the bottom right corner of the dialog box.
Step 3: “Result of Splitted Column”
Once, you click on ok button, it will splitting your column which you are selected earlier, here’s are the result of that column.
Before Splitting
After Splitting
Step 4: “Renaming a Column”
when you split the column for the first time, by default its name is set to be “Column 1”. if you want to rename a column, simply right-click on the column header and select “Rename” from the drop-down dialog box and click on that, once you click on that it will open for editing, type your desired name in the name box and click “Enter”
Step 5: “Close & Apply”
Once, you all set and you have made all the necessary changes, simply click on “Close & Apply” button located at the top left corner in the ribbon to apply the changes to your data.
Conclusion:
Splitting a column by number of characters in Power BI is a simple process that can help you to work with your data more effectively. By following the step by step process outlined in this article, you can easily split a column into multiple columns based on a specified delimiter.
