SOLVED

Split a column by rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1543283%22%20slang%3D%22en-US%22%3ESplit%20a%20column%20by%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543283%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20All%3C%2FP%3E%3CP%3EI%20need%20help%20in%20splitting%20a%20column%20where%20row%201%20is%20a%20username%20and%20row%202%20is%20an%20email%20address%20corresponding%20to%20that%20username.%3C%2FP%3E%3CP%3EThe%20data%20is%20presented%20in%20a%20single%20column%20as%20follows%3A%3C%2FP%3E%3CP%3EUsername%3C%2FP%3E%3CP%3EEmail%20address%3C%2FP%3E%3CP%3EUsername%3C%2FP%3E%3CP%3EEmail%20address%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20extract%20the%20email%20address%20into%20a%20second%20column%20and%20move%20it%20into%20the%20same%20row%20as%20the%20username.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20automate%20this%20process%20%2C%20so%20ideally%20to%20use%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1543283%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543317%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20a%20column%20by%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309813%22%20target%3D%22_blank%22%3E%40Stephen_Kane%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20sequence%20I%20think%20I%20need%20to%20follow%3A%3C%2FP%3E%3CP%3E1)%20Create%20a%20new%20column%3C%2FP%3E%3CP%3E2)%20If%20value%20in%20Column%201%20contains%20an%20%22%40%22%20symbol%2C%20cut%20and%20paste%20the%20value%20in%20Column%202%3C%2FP%3E%3CP%3E3)%20Remove%20all%20blank%20cells%20from%20Column%201%3C%2FP%3E%3CP%3E4)%20Remove%20all%20blank%20cells%20from%20Column%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20give%20me%3C%2FP%3E%3CP%3EColumn%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BColumn%202%3C%2FP%3E%3CP%3EUsername%26nbsp%3B%20%26nbsp%3B%20Email%20address%3C%2FP%3E%3CP%3EUsername%26nbsp%3B%20%26nbsp%3B%20Email%20address%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543319%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20a%20column%20by%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543319%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309813%22%20target%3D%22_blank%22%3E%40Stephen_Kane%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20convert%20your%20raw%20data%20into%20an%20Excel%20Table%20and%20rename%20the%20Excel%20Table%20as%20%22UserData%22%2C%20you%20may%20create%20a%20blank%20query%20and%20place%20the%20following%20M%20code%20into%20Advanced%20Editor%20and%20load%20the%20data%20back%20to%20the%20Sheet.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22UserData%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Added%20Index%22%20%3D%20Table.AddIndexColumn(Source%2C%20%22Index%22%2C%200%2C%201)%2C%0A%20%20%20%20%23%22Inserted%20Modulo%22%20%3D%20Table.AddColumn(%23%22Added%20Index%22%2C%20%22Modulo%22%2C%20each%20Number.Mod(%5BIndex%5D%2C%202)%2C%20type%20number)%2C%0A%20%20%20%20%23%22Pivoted%20Column%22%20%3D%20Table.Pivot(Table.TransformColumnTypes(%23%22Inserted%20Modulo%22%2C%20%7B%7B%22Modulo%22%2C%20type%20text%7D%7D%2C%20%22en-US%22)%2C%20List.Distinct(Table.TransformColumnTypes(%23%22Inserted%20Modulo%22%2C%20%7B%7B%22Modulo%22%2C%20type%20text%7D%7D%2C%20%22en-US%22)%5BModulo%5D)%2C%20%22Modulo%22%2C%20%22UserName%22)%2C%0A%20%20%20%20%23%22Filled%20Down%22%20%3D%20Table.FillDown(%23%22Pivoted%20Column%22%2C%7B%220%22%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Filled%20Down%22%2C%20each%20(%5B1%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Filtered%20Rows%22%2C%7B%22Index%22%7D)%2C%0A%20%20%20%20%23%22Renamed%20Columns%22%20%3D%20Table.RenameColumns(%23%22Removed%20Columns%22%2C%7B%7B%220%22%2C%20%22UserName%22%7D%2C%20%7B%221%22%2C%20%22Email%20Address%22%7D%7D)%0Ain%0A%20%20%20%20%23%22Renamed%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20output%20will%20be%20like%20this...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Power%20Query.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207631i56230E33C8D9B288%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Power%20Query.jpg%22%20alt%3D%22Power%20Query.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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:

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

7 Replies

@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

best response confirmed by Stephen_Kane (New Contributor)
Solution

@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...

Power Query.jpg

Please refer to the attached for more details.

 

@Subodh_Tiwari_sktneer 

Brilliant!

Thanks you so much, this works.

Regards

Stephen

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

@Subodh_Tiwari_sktneer 

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
123Quantity1
123Amount$50
123Product NameTest Product 1
123CategoryTest Category 1
123Quantity2
123Amount$100
123Product NameTest Product 2
123CategoryTest Category 2
234Quantity1
234Amount$70
234Product NameTest Product 3
234CategoryTest Category 3
234Quantity1
234Amount$100
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!

@Lor3nzo 

 

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.

 

let
    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}})
in
    #"Changed Type"

 

UnStackedData.jpg

@Subodh_Tiwari_sktneer thank you very much for your kind reply.

 

It works perfectly!

Next time I will follow your advice.

 

Thank you again