Forum Discussion

DON_DCS's avatar
DON_DCS
Copper Contributor
Jul 06, 2023

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 06, 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 20, 2023
SergeiBaklan I'm thinking if it's possible to use Table.ReplaceValue to override the project columns with the allocation calculation, i.e. eliminate the additional columns creation?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 20, 2023

    DON_DCS 

    See Table4 in Sheet3:

     

    // Table4
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    PCols = 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(PCols, each {_, type number})
    ),
    ReplacedValues = Table.ReplaceValue(ChangedTypes, each [Cost], null,
        (p,c,n) as number => p*c,
        PCols
    )
in
    ReplacedValues
    • DON_DCS's avatar
      DON_DCS
      Copper Contributor
      Jul 21, 2023
      Great solution Lorenzo - Thank you so much!
      The key step here is to change the type of the list to Number
      Table.TransformColumnTypes(Source,List.Transform(PCols, each {_, type number})
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 21, 2023

        Glad this helps DON_DCS 

         

        The key step here is to change the type of the list to Number
        Table.TransformColumnTypes(Source,List.Transform(PCols, each {_, type number})

        This doesn't change the list items to type Number, this changes the list of column names to type Number. And - IMHO - the other key point is we maintain the column type during the replacement with:

        (p,c,n) as number => p*c

        Note that this works because the columns were previously typed as number otherwise this would have no effect

         

        FYI A couple of months ago the exact same case was raised on MS Answers. After testing various options on a table of 100k rows with 50 columns to update (the OP had > 90), the following approach was +/- 2 x faster than the Table.ReplaceValue approach:

         

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

         

Resources