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                            Value 1   Value 2

Company A (Column 1)      Skill       Skill 

Company A (Column 1)      Skill       Skill 

Company B (Column 1)      Skill       Skill 

Company B (Column 1)      Skill       Skill 

Company B (Column 1)      Skill       Skill 

 

Data Format currently: One row per skill

company           Value

Company A       Skill 

Company A       Skill 

Company A       Skill 

Company A       Skill

Company B       Skill 

Company B       Skill 

Company B       Skill 

Company B       Skill 

Company B       Skill 

Company B       Skill 

 

There are varying amounts of skills per company but I need them to be in two columns, with as many rows per company as there are skills, but they all need to be unique skill they can't be duplicates. 

 

Can someone please assist? I can't think of a way to do this. 

Need Help
  • swatsonlord's avatar
    swatsonlord
    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"

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    1 year ago

    NewStep=Table.Combine(Table.Group(YourTable,"company",{"n",each #table({"company","Value1","Value2"},List.Transform(List.Split([Value],2),(x)=>{[company]{0}}&(if x{1}?=null then x&{null} else x)))})[n])

    • swatsonlord's avatar
      swatsonlord
      Frequent Visitor
      1 year ago

      Hey, 

      Thanks for that, it came up with the below error when I tried it - could you explain to me how it worked?

       

      Expression.Error: A cyclic reference was encountered during evaluation.

      Thanks, 

  • AlienSx's avatar
    AlienSx
    Super User
    1 year ago
        Table.FromList(
        List.Combine(
            Table.Group(
                your_table, // <--- your source table
                "company", 
                {"x", (x) => ((w) => List.Transform(List.Split(x[Value], 2), (z) => {w} & z))(Table.FirstValue(x))}
            )[x]
        ),
        (x) => x, 
        type table [Company = text, Value1 = text, Value2 = text],        
        null
    )
  • swatsonlord's avatar
    swatsonlord
    Frequent Visitor
    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"