Forum Discussion

megjarvis's avatar
megjarvis
Regular Visitor
3 years ago
Solved

Turn repeating row values into column headers?

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 th...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    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"

     

     

ThxAlot's avatar
ThxAlot
Super User
3 years ago
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