Split a column by rows

[Stephen_Kane]

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

[#2]

@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

[#3 · Accepted 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...

Please refer to the attached for more details.

[#4]

@Subodh_Tiwari_sktneer 

Brilliant!

Thanks you so much, this works.

Regards

Stephen

[#5]

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

[#6]

@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 ID	Attributes	Values
123	Quantity	1
123	Amount	$50
123	Product Name	Test Product 1
123	Category	Test Category 1
123	Quantity	2
123	Amount	$100
123	Product Name	Test Product 2
123	Category	Test Category 2
234	Quantity	1
234	Amount	$70
234	Product Name	Test Product 3
234	Category	Test Category 3
234	Quantity	1
234	Amount	$100
234	Product Name	Test Product 1
234	Category	Test 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!

[#7]

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

[#8]

@Subodh_Tiwari_sktneer thank you very much for your kind reply.

It works perfectly!

Next time I will follow your advice.

Thank you again