Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to 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"
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
)
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,
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |