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

[swatsonlord]

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. 

[swatsonlord · Accepted solution]

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"

[AlienSx]

    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
    )

[wdx223_Daniel]

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]

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,