cancel
Showing results 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

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
Regular Visitor

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

// Pair up the unique skills into groups of two
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
),
#"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 4
Regular Visitor

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

// Pair up the unique skills into groups of two
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
),
#"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"

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

Regular 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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors