Help with power query

Really fun challenge! You can make smart use of Splitter.SplitByCharacterTransition in this scenario.

Here's the alternative approach: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4hKTVTSUXLMSykC0eb65vpGBkamQKalvpEhiG0JZBuZGgBJQyMQYW4AYhsDRWJ1opW8EgsS84B8p8SsxCzcBhiCDTA2QRhgaGSsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), {"ProjectRef", "ModelDataSet", "ProjectStart", "ProjectEnd", "FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}),    
    // Sample binary data used for testing. Use the following line instead to get data from the table in the real implementation. 
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"ProjectRef", type text}, {"ModelDataSet", type text}, {"ProjectStart", type date}, {"ProjectEnd", type date}, {"FacilitiesUnitRate", Int64.Type}, {"FacilitiesQuantity", Int64.Type}, {"ServiceUnitRate", Int64.Type}, {"ServiceQuantity", Int64.Type}}, "en-US" ),
    Unpivot = Table.UnpivotOtherColumns(ChangeTypes, {"ProjectRef", "ModelDataSet", "ProjectStart", "ProjectEnd"}, "Attribute", "Value"),
    SplitAttribute = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"} ), {"Category", "Attribute"}),
    Pivot = Table.Pivot(SplitAttribute, List.Distinct(SplitAttribute[Attribute]), "Attribute", "Value", List.Sum),
    #"Sorted Rows" = Table.Sort(Pivot,{{"ModelDataSet", Order.Ascending}, {"Category", Order.Ascending}})
in
    #"Sorted Rows"

Thanks for prepping the initial dataset smozgur 

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

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