Forum Discussion

Stephen_Kane's avatar
Stephen_Kane
Copper Contributor
Jul 24, 2020
Solved

Split a column by rows

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:

Username

Email address

Username

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?

Thanks

BI & Data Analysis
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Jul 24, 2020

    Stephen_Kane 

     

    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.

    let
    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"}})
in
    #"Renamed Columns"

     

    The output will be like this...

    Please refer to the attached for more details.

     

7 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Jul 24, 2020

    Stephen_Kane 

     

    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.

    let
    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"}})
in
    #"Renamed Columns"

     

    The output will be like this...

    Please refer to the attached for more details.

     

  • Stephen_Kane's avatar
    Stephen_Kane
    Copper Contributor
    Jul 24, 2020

    Stephen_Kane 

    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

Resources