Help with power query

[cool_333]

Hi, guys need help with power query transformation below is the raw data that need the output as 2nd table. Thanks in advance.

Raw data

[Rickmaurinus]

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.

[smozgur]

---

Thanks for prepping the initial dataset @smozgur 

👍

Testing your solution @Rickmaurinus. Looks interesting.

Edit:  No, it is not interesting, it is beautiful! Thanks for posting it!

[ronrsnfld]

Another approach is, after unpivoting, group by the first five columns, and aggregate by  pivoting each subtable

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],

//set data types
    #"Changed Type" = 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}}),

//Unpivot the Rate and Quantity columns
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type",
         {"ProjectRef", "ModelDataset", "ProjectStart", "ProjectEnd"}, "Attribute", "Value"),

//Split the Attribute Column retaining the first lower to upper case transition
// and label appropriately
    #"Split Column by Character Transition" = 
        Table.SplitColumn(#"Unpivoted Columns", "Attribute", 
            Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), 
            {"Category", "Attribute"}),

//Group rows by the first five columns
    #"Grouped Rows" = Table.Group(#"Split Column by Character Transition", 
        {"ProjectRef", "ModelDataset", "ProjectStart", "ProjectEnd", "Category"}, {

        //Aggregate by pivoting each subgroup on the Attribute column
            {"Aggregate", (t)=>Table.Pivot(t,List.Distinct(t[Attribute]), "Attribute","Value"), 
                                type table[Unit=Currency.Type, Quantity=Int64.Type]}
        }),

//Expand the Tables column
    #"Expanded Aggregate" = Table.ExpandTableColumn(#"Grouped Rows", "Aggregate", {"Unit", "Quantity"}, {"Unit Rate", "Quantity"})
in
    #"Expanded Aggregate"

[smozgur]

I am very interested in seeing another way of doing this since I am not very good at using Pivot columns.

Until someone else comes up with a better approach, here is my way:

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}}),
    UnpivotColumns = Table.Unpivot(ChangeTypes, {"FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}, "Category", "Value"),
    Quantity = Table.AddIndexColumn(Table.RenameColumns(Table.ReplaceValue(Table.SelectRows(UnpivotColumns, each Text.EndsWith([Category], "Quantity")),"Quantity","",Replacer.ReplaceText,{"Category"}), {{"Value", "Quantity"}}), "Index"),
    UnitRate = Table.AddIndexColumn(Table.RenameColumns(Table.ReplaceValue(Table.SelectRows(UnpivotColumns, each Text.EndsWith([Category], "UnitRate")),"UnitRate","",Replacer.ReplaceText,{"Category"}), {{"Value", "Unit Rate"}}), "Index"),
    JoinOnIndex = Table.NestedJoin(UnitRate, "Index", Quantity, "Index", "Join"),
    ExpandQuantity = Table.ExpandTableColumn(JoinOnIndex, "Join", {"Quantity"}),
    RemoveIndex = Table.RemoveColumns(ExpandQuantity,{"Index"})
in
    RemoveIndex

[jgordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4hKTVTSUXLMSykC0eb65vpGBkamQKalvpEhiG0JZBuZGgBJQyMQYW4AYhsDRWJ1opW8EgsS84B8p8SsxCzcBhiCDTA2QRhgaGSsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), {"ProjectRef", "ModelDataSet", "ProjectStart", "ProjectEnd", "FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}),    
    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}}),
    AddCategoryColumn = Table.AddColumn(ChangeTypes, "Category", each {"Facilities", "Service"}),
    ExpandCategory = Table.ExpandListColumn(AddCategoryColumn, "Category"),
    AddRecordColumn = Table.AddColumn(ExpandCategory, "Custom", each 
        if [Category] = "Facilities" then [Rate = [FacilitiesUnitRate], Quantity = [FacilitiesQuantity]] else [Rate = [ServiceUnitRate], Quantity = [ServiceQuantity]]),
    RemoveColumns = Table.RemoveColumns(AddRecordColumn,{"FacilitiesUnitRate", "FacilitiesQuantity", "ServiceUnitRate", "ServiceQuantity"}),
    Result = Table.ExpandRecordColumn(RemoveColumns, "Custom", {"Rate", "Quantity"})
in
    Result

[smozgur]

Very nice!