Jul 24 2020 01:27 AM
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
Jul 24 2020 02:10 AM
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
Jul 24 2020 02:10 AM
Solution
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.
Jul 24 2020 03:14 AM
Mar 13 2021 10:34 AM
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!
Mar 13 2021 09:55 PM
Ideally you should have opened your own question Anyways follow the steps given below...
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"
Mar 14 2021 01:23 PM
@Subodh_Tiwari_sktneer thank you very much for your kind reply.
It works perfectly!
Next time I will follow your advice.
Thank you again
Jul 24 2020 02:10 AM
Solution
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.