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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
vsilaire
New Member

Pivot columns in a specific way

Hi,

For a school project I have these columns in my Power Query : 

 

Total votes | Place | Candidate 01 | C01 Votes | Candidate 02 | C02 Votes | Candidate 03 | C03 Votes

 

And I need to pivot them like this : 

Total votes | Place | Candidate | Votes |

(and then all the data from Candidate 01, 02, 03 would go in "Candidate" and all from Votes 01, 02, 03 would go to "Votes")

 

How can I do this properly?

 

Thank you very much!

Have good day.

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi, @vsilaire 

another solution

 

Table.FromRows(
List.Combine(
Table.ToList(
Your_Source,
(x) => List.Transform(
List.Split(List.RemoveFirstN(x, 2), 2),
(y) => List.FirstN(x, 2) & y))),
{"Total votes", "Place", "Candidate", "Votes"})

 Stéphane

View solution in original post

4 REPLIES 4
slorin
Super User
Super User

Hi, @vsilaire 

another solution

 

Table.FromRows(
List.Combine(
Table.ToList(
Your_Source,
(x) => List.Transform(
List.Split(List.RemoveFirstN(x, 2), 2),
(y) => List.FirstN(x, 2) & y))),
{"Total votes", "Place", "Candidate", "Votes"})

 Stéphane

manvishah17
Responsive Resident
Responsive Resident

Hey, I have created sample data and tried your query on it , 
So this was my base table Screenshot 2024-04-30 161351.png

 and I tried to achieve this , with the help of pivoting. Select all candidate columns and pivot it and do same for votes columns . Then remove the extra Candidate and votes columns.

These  are my steps :
let
Source = Excel.Workbook(File.Contents("C:\Users\Downloads\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Total Votes", Int64.Type}, {"Place", Int64.Type}, {"Candidate 01", type text}, {"C01 Votes", Int64.Type}, {"Candidate 02", type text}, {"C02 Votes", Int64.Type}, {"Candidate 03", type text}, {"C03 Votes", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Total Votes", "Place", "C01 Votes", "C02 Votes", "C03 Votes"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Total Votes", "Place", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute", "Attribute.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Candidate"}, {"Value.1", "Votes"}})
in
#"Renamed Columns"
Screenshot 2024-04-30 161503.png

 

 If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 
dufoq3
Super User
Super User

Hi @vsilaire, different approach here.

 

Result

dufoq3_0-1714458834857.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJT0lEKyElMTjUEMpxBBJhhBCRMQQxjIGFkoBSrE61kbABTawRXawBTbA5TbGisFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total votes" = _t, Place = _t, #"Candidate 01" = _t, #"C01 Votes" = _t, #"Candidate 02" = _t, #"C02 Votes" = _t, #"Candidate 03" = _t, #"C03 Votes" = _t]),
    Transform = [ a = Table.ColumnNames(Source),
    b = List.FirstN(a, 2), //Header columns
    c = List.Count(List.Select(a, each Text.EndsWith(_, "votes", Comparer.OrdinalIgnoreCase) and not Text.Contains(_, "total", Comparer.OrdinalIgnoreCase))), //Candidate count
    d = List.Count(List.RemoveMatchingItems(a, b)) / c, //Column count for each candidate
    e = List.TransformMany(
            Table.ToRows(Source),
            each List.Split(List.Skip(_, 2), d),
            (x,y)=> List.FirstN(x, List.Count(b)) & y ),
    f = Table.FromRows(e, List.FirstN(a, List.Count(b) + d)),
    g = Table.TransformColumnNames(f, each Text.Combine(List.Select(Text.Split(_, " "), (x)=> Text.Length(Text.Select(x, {"0".."9"})) = 0), " ")) //Renamed columns
  ][g]
in
    Transform

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

slorin
Super User
Super User

Hi

 

= #table(
{"Total votes", "Place", "Candidate", "Votes"},
List.TransformMany(
Table.ToRows(YourSource),
each {1..Table.ColumnCount(YourSource)/2-1},
(x,y)=> {x{0}, x{1}, x{y*2}, x{y*2+1}})
)

 

or

let
Source = YourSource,
NbCandidates = Table.ColumnCount(Source)/2-1,
ToColumns = Table.ToColumns(Source),
Pivot = Table.FromColumns(
{List.Repeat(ToColumns{0}, NbCandidates),
List.Repeat(ToColumns{1}, NbCandidates),
List.Combine(List.Skip(List.Alternate(ToColumns, 1, 1, 1))),
List.Combine(List.Skip(List.Alternate(ToColumns, 1, 1)))},
{"Total votes", "Place", "Candidate", "Votes"})
in
Pivot

Stéphane

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors