Pivot columns in a specific way

[vsilaire]

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.

[slorin · Accepted solution]

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]

Hey, I have created sample data and tried your query on it , 
So this was my base table 

 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"

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

[dufoq3]

Hi @vsilaire, different approach here.

Result

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

[slorin]

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