Turn repeating row values into column headers?

[megjarvis]

I have a dataset that tracks the progress of various milestones across multiple projects. The projects are in rows and multiple attributes about each milestone are tracked in columns. I track both the name of the milestone and the percent completion of each milestone in separate columns. So if there are four milestones, I use eight columns to track them.

What I start with:

I want to get this data into an unpivoted table with the columns being: Project, Milestone, Name, Percent Complete.

What I need:

I can unpivot it and get the Project and Milestone columns, but the final two columns are Attribute (with values alternating between "Name" and "Percent Complete") and Value (with the values alternating between the actual names of the milestones and the actual percent completion numbers). 

What I can get to:

I have managed to get to the final product I'm looking for but it requires three extra tables per milestones, which seems ludicrous.  I think there must be a method that I'm just not aware of. Transpose doesn't work, because it will only transpose the entire table, and pivot doesn't work because I lose the text values in the cells. This feels like something that shouldn't be too complicated, though. Can you help?

Here's a link to the dummy data: 

https://docs.google.com/spreadsheets/d/1NogjwuUN7kTU0jDex5vmje8fnWURgEUARqK2Zo5Ghj8/edit?usp=sharing

Thanks for your help solving this mystery for me!

[ronrsnfld · Accepted solution]

After doing your unpivot, you can

• Split out the Number for the Milestone column
• Add an extra column that is shifted by one from the Value column
• Remove every other row from the table, starting with row 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Project", type text}, {"Milestone 1 - Name", type text}, {"Milestone 1 - Percent Complete", type number}, {"Milestone 2 - Name", type text}, {"Milestone 2 - Percent Complete", type number}, {"Milestone 3 - Name", type text}, {"Milestone 3 - Percent Complete", type number}, {"Milestone 4 - Name", type text}, {"Milestone 4 - Percent Complete", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    
    #"Add Milestone" = Table.AddColumn(#"Unpivoted Other Columns", "Milestone", 
        each Number.From(Text.Split([Attribute]," "){1}), type number),
    #"Reordered Columns" = Table.ReorderColumns(#"Add Milestone",{"Project", "Milestone", "Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),

//Offset Percentage column
    #"Add Offset Col" = Table.FromColumns(
        Table.ToColumns(#"Removed Columns") &
        {List.RemoveFirstN(#"Removed Columns"[Value]) & {null}},
        type table[Project=text, Milestone=Int64.Type, Name=text, Percent Complete = Percentage.Type]),
        
    #"Removed Alternate Rows" = Table.AlternateRows(#"Add Offset Col",1,1,1)
in
    #"Removed Alternate Rows"

[megjarvis]

AH-MAZING! Thank you so much! 

I had to add a step to replace null percent complete values with 0s so my unpivot would result in perfect alternating rows, but it works! I love the outside-the-box thinking. I will review this more closely and see if I can replicate the outcome in my other report where I do the same thing.  Thanks again! 

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJBsGNKDQyMzIsVfDNzUotL8vNSFQyB0kamqvgVGQGlLQwIKDIGShsTMskEKG0GNClWB+FCJyQtTthdiGI3FjVGRKgxJkKNCUQNsuuckXQ4Y3edoQGKuVhUgdxnSkiRMUZUYFGExYUuSDpcsLsQ1W4sijAdiEUR2IGEFJmAAwTowlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Milestone 1 - Name" = _t, #"Milestone 1 - Percent Complete" = _t, #"Milestone 2 - Name" = _t, #"Milestone 2 - Percent Complete" = _t, #"Milestone 3 - Name" = _t, #"Milestone 3 - Percent Complete" = _t, #"Milestone 4 - Name" = _t, #"Milestone 4 - Percent Complete" = _t]),
    Cols = Table.ToColumns(Source),
    Tables = let col = Cols{0} in List.Transform(List.Split(List.Skip(Cols),2), each Table.FromColumns({col} & _, {"Project","Name","Pct Completed"})),
    Table = Table.Combine(Tables)
in
    Table