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