Excel BI Power Query Challenge 43 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    UnpivotOther = Table.UnpivotOtherColumns(AddIndex, {"Name", "Date", "Hours", "Index"}, "Attribute", "Value"),
    Group = Table.Group(UnpivotOther, {"Index"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Name=text, Date=datetime, Hours=number, Index=number, Attribute=text, Value=text]}}),
    Expand = Table.ExpandTableColumn(Group, "All", {"Name", "Date", "Hours", "Attribute", "Value"}, {"Name", "Date", "HoursX", "Attribute", "Value"}),
    AddHours = Table.AddColumn(Expand, "Hours", each [HoursX]/[Count]),
    RemoveCols = Table.RemoveColumns(AddHours,{"Index", "Count", "HoursX"}),
    ReType = Table.TransformColumnTypes(RemoveCols,{{"Date", type date}}),
    AddIndex0 = Table.AddIndexColumn(ReType, "Index", 0, 1, Int64.Type),
    PivotSub = Table.Pivot(AddIndex0, List.Distinct(AddIndex0[Attribute]), "Attribute", "Value"),
    Remove2 = Table.RemoveColumns(PivotSub,{"Index"})
in
    Remove2