![]() ![]() So, we once again click the Transform > Split Column > By Delimiter command. Now, we need to split the Custom column again, this time at each colon into columns. We click OK, and bam … the results are shown below. The Split Column by Delimiter dialog opens, and we select the Semicolon delimiter, expand the Advanced options, and pick Rows, as shown below. ![]() We select the Custom column, and then the Transform > Split Column > By Delimiter command. Then, we’ll split the column at each colon into columns.įirst, let’s split the column at each semicolon into rows. Bill Jelen mentioned this in his year-end tips for the MyExcelOnline podcast, and if you’d like to check it out, it is here. First, we’ll split the column at each semicolon into rows. That means that we really need to split the Custom column twice. We want to see each attribute:value pair in one row and two columns. The Custom column for each order contains one or more attribute:value pairs, separated by a colon. ![]() Now, it is time to perform the transformations. The Power Query window is displayed, as shown below. To get the data table into Power Query, we select any cell in the table and select the Data > From Table/Range command. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in. Note: The steps below are presented with Excel for Windows 2016. We’ll use Power Query to help us out as follows: Let’s say our export contains a delimited list of values, as shown below.īut, we need to split the delimited list and put them into rows, like this:Įach OrderID may have a variable number of rows, for example, some orders have 4 rows and some 3. Well, what are we waiting for … let’s get to it! Objective In this post, we’ll do the opposite and convert a delimited list into rows. In the first post, we combined rows into a delimited list. This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |