Forum Discussion
Split one column, into two columns. Multiple rows containing skills for the same company
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"
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
)