We then Split those values into separate Columns using the semicolon as the delimiter.Extract the values from the List using a semicolon separator (or some other token not likely to appear in the data).We add a Custom Column which converts the resultant table into a List and then.Now we Group By the Integer-Division and Project Name columns with Operation:= All Rows (no aggregation).Based on the Index column, add an Integer/Divide column dividing by 4.For grouping of the data into appropriate rows:.Split the column on the transition from letter to digit.To remove that number after the unpivot, select the new Attribute column.Because this has been converted into a Table, the duplicate column names in the original range will be converted by have a sequential number appended.Select Project Name and unpivot other columns.Import the Table ( PQ will turn a range into a Table, or you can do this yourself).If it really contains text, some changes will be required in the Query. I have Also assumed that the Consumed Time column contains formatted Integers.I have assumed that columns, if added, will always be in Groups of 4 ( Task Name | Start Time | End Time | Consumed Time).So you need to modify the actual M-Code that is generated.
#Excel split cells into rows by semicolon free
To transform your Table1 to Table2, as in your first screen shot, you can use Power Query available in Excel 2010+ either as a free add-in from MS, or built-in in later versions.Īlthough the transformation can be done from the GUI, that will return errors if you expand the number of columns in your data.