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

[Craigc3814]

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]

in which environment are you looking to solve this?

 

[Craigc3814]

in which environment are you looking to solve this?

Powerquery sorry

 

[VBA Geek]

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]

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

 

[harry780]

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]

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]

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

 

[VBA Geek]

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

 

[Fractalis]

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