Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
swatsonlord
Regular Visitor

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. 

1 ACCEPTED SOLUTION
swatsonlord
Regular Visitor

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"

View solution in original post

4 REPLIES 4
swatsonlord
Regular Visitor

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
Super User
Super User

    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
Super User
Super User

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])

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, 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors