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
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.
Solved! Go to 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
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
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.
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |