Forum Discussion

swatsonlord's avatar
swatsonlord
Frequent Visitor
1 year ago
Solved

Split one column, into two columns. Multiple rows containing skills for the same company

Hi There,    We need to get our data into a particular format using Power Query in Excel    Format required  - as many rows as required to have unique skills in two columns per company  company ...
  • swatsonlord's avatar
    1 year ago

    Thanks for your help, I needed to add in a couple of steps but your answers helped me get there: 

    let
    Source = Skills,

    // Group by company and collect all skills
    GroupedRows = Table.Group(Source, {"company"}, {{"AllSkills", each _}}),

    // Add a column to display unique skills for each company
    AddUniqueSkills = Table.AddColumn(GroupedRows, "UniqueSkills", each List.Distinct([AllSkills][Value])),

    // Pair up the unique skills into groups of two
    AddSkillPairs = Table.AddColumn(AddUniqueSkills, "SkillPairs", each
    let
    UniqueSkills = [UniqueSkills],
    SkillPairs = List.Transform(List.Split(UniqueSkills, 2), (x) =>
    if List.Count(x) = 2
    then {x{0}, x{1}}
    else {x{0}, null}) // Handle odd number of skills
    in
    SkillPairs
    ),
    #"Expanded SkillPairs" = Table.ExpandListColumn(AddSkillPairs, "SkillPairs"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded SkillPairs", {"SkillPairs", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "SkillPairs", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"SkillPairs.1", "SkillPairs.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SkillPairs.1", type text}, {"SkillPairs.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AllSkills", "UniqueSkills"})
    in
    #"Removed Columns"