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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors