# Convert 2nd column values to rows when column 1 is the same name



## Craigc3814 (Jul 20, 2018)

I have 2 columns

Column 1 is the name of a street and that street name is repeated for each task that was completed on the street.  I want to group the street name so that there is only 1 line but a new column for each task

10th street   Repair pothole
10th street   New Stop Sign
10th street   New curb
11th street   Repair Pothole
11th street   Add Stoplight

so the data would look like this when complete

10th street Repair pothole New stop sign new curb
11th street Repair pothole Add stoplight

Is this done with a modulo column? I cannot seem to remember how I figured it out in the past.


----------



## VBA Geek (Jul 20, 2018)

in which environment are you looking to solve this?


----------



## Craigc3814 (Jul 20, 2018)

VBA Geek said:


> in which environment are you looking to solve this?



Powerquery sorry


----------



## VBA Geek (Jul 20, 2018)

If you begin with a table, named "Data", like the one below:








The following M will achieve the below result:


```
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],


    Grp = Table.Group(Source, 
                      {"Street"}, 
                        {
                            {"Concat", each Text.Combine([Task],",")},
                            {"Count", each List.Count([Task]), Int64.Type}
                        },
                        GroupKind.Global),


    Final = Table.RemoveColumns(
                    Table.SplitColumn(Grp, 
                                      "Concat", 
                                       Splitter.SplitTextByDelimiter(","), 
                                       List.Max(Grp[Count]), 
                                        "",
                                        ExtraValues.Ignore),
                    {"Count"}
                    )


in
    Final
```


----------



## Craigc3814 (Jul 20, 2018)

This worked brilliantly. I had to add a step in to remove duplicate tasks per asset. THANK YOU!!!!!


----------



## harry780 (Mar 18, 2019)

I dont suppose you have any idea on how this code could be run but with python instead? I am generating a spreadsheet using python and I also wish to manipulate the fields like this within the code


----------



## VBA Geek (Mar 18, 2019)

In Python you can do it like this using _pandas_


```
import pandas as pd


data = {'Street': ['10th street'] * 3 + ['11th street'] * 2,
        'task': ['Repair Pothole', 'New Stop Sign', 'New Curb', 'Repair Pothole', 'Add StopLight']}


df = (pd.DataFrame(data)
      .groupby(by='Street')
      .apply(lambda grouped: grouped['task'].str.cat(sep="|"))
      .str.split(pat="|", expand=True))


print(df)
```


----------



## harry780 (Mar 18, 2019)

Many thanks, is there a way to write this without typing out the data ?


----------



## VBA Geek (Mar 18, 2019)

yes, but this depends on your data source. pandas supports various data sources you can build a dataframe from


----------



## Fractalis (Mar 18, 2019)

Another option:

1- ) Select your range and then insert a Pivot Table
2- ) Within the options select "Add this to the Data Model"
3- ) In Pivot Table drag Column1 fiel to "Rows"
4- ) Then in Pivot Table panel to the left, Rigth Click over your Table name and the option "Add Measure" will appear
5- ) Now in "Measure name" you could put something like "Task" as field name.
6- ) Wthing formula section enter this *=CONCATENATEX(Table1,Table1[Column2],", "*)
7- ) In Pivot Table panel will appears a new field "Fx Tasks"
8- ) Select Fx Task and drag it to "Values" window.
9- ) Remove Grand Totals row
10-) Done


----------



## Craigc3814 (Jul 20, 2018)

I have 2 columns

Column 1 is the name of a street and that street name is repeated for each task that was completed on the street.  I want to group the street name so that there is only 1 line but a new column for each task

10th street   Repair pothole
10th street   New Stop Sign
10th street   New curb
11th street   Repair Pothole
11th street   Add Stoplight

so the data would look like this when complete

10th street Repair pothole New stop sign new curb
11th street Repair pothole Add stoplight

Is this done with a modulo column? I cannot seem to remember how I figured it out in the past.


----------



## harry780 (Mar 19, 2019)

VBA Geek said:


> yes, but this depends on your data source. pandas supports various data sources you can build a dataframe from



I have a large excel spreadsheet that is generated from a python script containing data in this format:


```
[TABLE="width: 1301"]
<tbody>[TR]
[TD]GUID[/TD]
[TD]Chemical[/TD]
[TD]Association[/TD]
[TD]Term[/TD]
[/TR]
[TR]
[TD]{2EFD7D98-C03D-4821-B43F-BC1DBAD7F6B8}[/TD]
[TD](1,1-Dimethylethyl)benzaldehyde[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzaldehyde :- {(1,1-Dimethylethyl)benzaldehyde}[/TD]
[/TR]
[TR]
[TD]{2EFD7D98-C03D-4821-B43F-BC1DBAD7F6B8}[/TD]
[TD](1,1-Dimethylethyl)benzaldehyde[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzaldehyde[/TD]
[/TR]
[TR]
[TD]{09114BCC-72B9-4A86-A18C-5C60304C4944}[/TD]
[TD](1,1-Dimethylethyl)benzene[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzene[/TD]
[/TR]
[TR]
[TD]{09114BCC-72B9-4A86-A18C-5C60304C4944}[/TD]
[TD](1,1-Dimethylethyl)benzene[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzene :- {(1,1-Dimethylethyl)benzene}[/TD]
[/TR]
</tbody>[/TABLE]
```

But I am wanting to represent this data like this: 


```
[TABLE="width: 1051"]
<tbody>[TR]
[TD]GUID[/TD]
[TD]Chemical[/TD]
[TD]Association[/TD]
[TD]Term[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]{2EFD7D98-C03D-4821-B43F-BC1DBAD7F6B8}[/TD]
[TD](1,1-Dimethylethyl)benzaldehyde[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzaldehyde :- {(1,1-Dimethylethyl)benzaldehyde}[/TD]
[TD]tert-Butylbenzaldehyde[/TD]
[/TR]
[TR]
[TD]{09114BCC-72B9-4A86-A18C-5C60304C4944}[/TD]
[TD](1,1-Dimethylethyl)benzene[/TD]
[TD]exactMatch[/TD]
[TD]tert-Butylbenzene[/TD]
[TD]tert-Butylbenzene :- {(1,1-Dimethylethyl)benzene}[/TD]
[/TR]
</tbody>[/TABLE]
```

Is there a way to do this with pandas  ? 
Sorry I am fairly new to python programming
Many thanks


----------

