none
Create column names from column data RRS feed

  • Question

  • I want to see if I can transform my data as follows. Data source provides columns A unique column of records (such as an email address) and column B which contains a comma delimited list of values.

    I want to turn Column B into Columns C through F where the data in Column B becomes a unique column name (or adds to an existing column name) and enters "Yes" (or 1).

    

    I've tried many conbinations of Split, transpose, unpivot, etc. but couldn't get the results I'm looking for.

    Here's a link to this sample data.

    • https://1drv.ms/x/s!AkA1S7VOLQqgmhmokxVGzm0Pp9e1?e=R09MjV

    Thanks for your help!

    Thursday, January 23, 2020 2:07 AM

Answers

  • Hi Michael,

    You may use this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
        yes = Table.AddColumn(Source, "Yes", each "Yes"),
        split = Table.ExpandListColumn(Table.TransformColumns(yes, {"Data", Splitter.SplitTextByDelimiter(", ")}), "Data"),
        pivot = Table.Pivot(split, List.Distinct(split[Data]), "Data", "Yes")
    in
        pivot

    If you need to keep the order of rows, you may do it by adding (then sorting) index column.

    Thursday, January 23, 2020 9:14 AM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    All GUI's.
    Extra: Load into PP and count multiple items.
    http://www.mediafire.com/file/5m57lk3kw317im7/01_22_20.xlsx/file
    http://www.mediafire.com/file/iwlxzbu5gbjal58/01_22_20.pdf/file

    Thursday, January 23, 2020 5:24 AM
  • I hadn't considered using a PivotTable and that does give the results I was asked for, thank you!

    Would there be a way to do this all within Power Query so that I can get a regular Excel table as the output? That's what I'm currently doing with my spreadsheet, and then use Index/Match to pull in data from other tables. I've considered trying to do this all with PivotTables and relationships but that would be a longer term project for me.

    If it's not possible, I'll see what I can do to integrate the PivotTable into my workflow.

    Thursday, January 23, 2020 6:45 AM
  • Hi Michael,

    You may use this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
        yes = Table.AddColumn(Source, "Yes", each "Yes"),
        split = Table.ExpandListColumn(Table.TransformColumns(yes, {"Data", Splitter.SplitTextByDelimiter(", ")}), "Data"),
        pivot = Table.Pivot(split, List.Distinct(split[Data]), "Data", "Yes")
    in
        pivot

    If you need to keep the order of rows, you may do it by adding (then sorting) index column.

    Thursday, January 23, 2020 9:14 AM
  • Sorry, I did have errors in my output. I mistakenly made some last minute changes to the data and clearly didn't get the output right.

    I found that Aleksei's code worked best for me, but I truly appreciate all of the responses! I'm really pleased that I'm able to do this in Power Query.

    Thursday, January 23, 2020 10:05 AM
  • @Michael

    When I posted Aleksei's post wasn't "visible" and his solution is more straightforward than mine, so deleted my post & Upvoted Aleksei's option

    Thursday, January 23, 2020 11:55 AM
  • @Lz, It's always nice when there is more than one solution. Thanks again!
    Thursday, January 23, 2020 9:09 PM