Split a column by rows

New Contributor

Hi, All

I need help in splitting a column where row 1 is a username and row 2 is an email address corresponding to that username.

The data is presented in a single column as follows:


Email address


Email address


I need to extract the email address into a second column and move it into the same row as the username.


I need to automate this process , so ideally to use Power Query.


Any ideas?


7 Replies


Here's the sequence I think I need to follow:

1) Create a new column

2) If value in Column 1 contains an "@" symbol, cut and paste the value in Column 2

3) Remove all blank cells from Column 1

4) Remove all blank cells from Column 2


This should give me

Column 1     Column 2

Username    Email address

Username    Email address

best response confirmed by Stephen_Kane (New Contributor)



If you convert your raw data into an Excel Table and rename the Excel Table as "UserData", you may create a blank query and place the following M code into Advanced Editor and load the data back to the Sheet.

    Source = Excel.CurrentWorkbook(){[Name="UserData"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "UserName"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"0"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([1] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"0", "UserName"}, {"1", "Email Address"}})
    #"Renamed Columns"


The output will be like this...

Power Query.jpg

Please refer to the attached for more details.




Thanks you so much, this works.



You're welcome Stephen! Glad it worked as desired.


I'm stuck on a similar issue of @Stephen_Kane.

I'm new here in the Community and I hope that @Subodh_Tiwari_sktneer or someone else can help me to come up with a solution.


I have 3 columns as follow:

Order IDAttributesValues
123Product NameTest Product 1
123CategoryTest Category 1
123Product NameTest Product 2
123CategoryTest Category 2
234Product NameTest Product 3
234CategoryTest Category 3
234Product NameTest Product 1
234CategoryTest Category 1


As you can see I have duplicate ATTRIBUTES and different type of VALUES (numbers, currencies and text).


I need to split each single attribute in columns with the corresponding value below.


Thank you in advance for helping me with this!



Ideally you should have opened your own question Anyways follow the steps given below...


  1. Convert your source data into an Excel Table and rename it as "Raw_Data".
  2. Create a Blank Query, open the Advanced Editor and replace the existing content with the query given below.
  3. On Home Tab, click on Close & Load to and choose the destination for the output table.


    Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Pivoted Column" = Table.Pivot(#"Inserted Modulo", List.Distinct(#"Inserted Modulo"[Attributes]), "Attributes", "Values"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"Quantity", "Amount", "Product Name", "Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Order ID", Int64.Type}, {"Quantity", Int64.Type}, {"Amount", Currency.Type}, {"Product Name", type text}, {"Category", type text}})
    #"Changed Type"



@Subodh_Tiwari_sktneer thank you very much for your kind reply.


It works perfectly!

Next time I will follow your advice.


Thank you again