Forum Discussion

DON_DCS's avatar
DON_DCS
Copper Contributor
Jul 05, 2023
Solved

Power Query List

Hi all, could anyone please help with M language to show how to calculate project allocation dynamically when number of projects can be changed in the example provided, e.g. Project 1 allocation = Co...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 05, 2023

    DON_DCS Perhaps the attached example helps you on your way. It's a rather simplistic, step by step approach that probably can be made more effective, but then it would most likely be more difficult to follow.

     

    The query unpivots the data first. Then it adds the calculation of the allocation for each row and adds a column to hold allocation column names.

    Then it pivots only the columns relevant to the allocations and merges it with the original source, based on the project name. Expand the merged column based on the dynamic list of allocation column names created earlier.

     

    See if you can follow the step and apply them to your own file. If not come back here.

DON_DCS's avatar
DON_DCS
Copper Contributor
Jul 24, 2023
This is genius Lorenzo I've tested adding 12K rows and 200 columns, the Table.AddColumn approach is at least 15 seconds faster.
Still try to understand the TypeForColumnsToUpdate and why did you put a false after that and type table TypeForColumnsToUpdate after Table.AddColumn 🙂
Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 25, 2023

DON_DCS 

 

Still try to understand the TypeForColumnsToUpdate and why did you put a false
TBH I have no idea why this must be a closed record (open=false)

 

Below is another option:

// Table6
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    ColumnsToUpdate = List.Select(Table.ColumnNames(Source),
        (c)=> Text.StartsWith(c,"P")
    ),
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"Name", type text}, {"Division", type text}, {"Cost", type number}} &
        List.Transform(ColumnsToUpdate, each {_, type number})
    ),
    AddedTable = Table.AddColumn(ChangedTypes, "TEMP_TABLE",
        (r)=> Table.FromRows( 
            {List.Transform(Record.ToList(Record.SelectFields(r,ColumnsToUpdate)), each _ * r[Cost])},
            ColumnsToUpdate
        ),
        type table
    ),
    RemovedColumns = Table.RemoveColumns(AddedTable, ColumnsToUpdate),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "TEMP_TABLE", ColumnsToUpdate),
    RestoredTypes = Value.ReplaceType(ExpandedTable, Value.Type(ChangedTypes))
in
    RestoredTypes

Now, if the output of your query loads to an Excel sheet only you can do it with untyped columns, i.e.:

// Table7
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    ColumnsToUpdate = List.Select(Table.ColumnNames(Source),
        (c)=> Text.StartsWith(c,"P")
    ),
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"Name", type text}, {"Division", type text}, {"Cost", type number}}
    ),
    AddedTable = Table.AddColumn(ChangedTypes, "TEMP_TABLE",
        (r)=> Table.FromRows( 
            {List.Transform(Record.ToList(Record.SelectFields(r,ColumnsToUpdate)), each _ * r[Cost])},
            ColumnsToUpdate
        ),
        type table
    ),
    RemovedColumns = Table.RemoveColumns(AddedTable, ColumnsToUpdate),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "TEMP_TABLE", ColumnsToUpdate)
in
    ExpandedTable

Resources