Power Query Custom Function Calculation Across Multiple Columns

Question

I have a power query table that contains 52 columns. The first two columns contain percentage values for each row, and the remaining 50 columns contain numeric values. I'd like to use a transform function to apply a calculation to the numeric values which draws from the percentage values (see ApplyCalculation in code below), but I'm coming up empty. I'd have thought this would have been pretty simple, but I'm starting to think I won't be able to do this without creating 50 new columns, each calculating for each existing column. I'd like to avoid this if at all possible.

This is my current code (for ease I've reduced the number of numeric columns down to 3, rather than 50. I figure what works for 3 will work for 50).

let
    // Define the custom function for the calculation
    ApplyCalculation = (value, Percentage1, Percentage2) =>
        if value > 1 then ((value - 1) * Percentage1) + 1
        else 1 - ((1 - value) * Percentage2),
    
    Source = #"Rates Table",
    #"Merged Queries" = Table.NestedJoin(Source, {"MONTH"}, #"Contract Details", {"Month"}, "Contract Details", JoinKind.Inner),
    #"Expanded Contract Details" = Table.ExpandTableColumn(#"Merged Queries", "Contract Details", {"Percentage 1", "Percentage 2"}, {"Percentage 1", "Percentage 2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Contract Details",{"DNO", "MONTH", "DATE", "Percentage 1", "Percentage 2", "1", "2", "3"}),
    
// Apply the calculation to columns "1" to "50" with proper parameter values
        CustomTransform = Table.AddColumn(#"Reordered Columns", "Calculated Columns", each Record.TransformFields(_, List.Transform(List.Transform({1..3}, each Text.From(_)), each [Name = _ & "_Calculated", Value = ApplyCalculation(_[Column1], [Percentage 1], [Percentage 2])])))
in
    CustomTransform

The above code doesn't error, but fails to apply any calculation to the numbers. Checking the figures before and after the CustomTransform step confirms they haven't changed. I've tested the custome function in an isolated query with hardcoded values for the 'value', 'Percentage1' and 'Percentage2' parameters, but I don't know why it won't work here. I read something online about a similar issue being down to the function receiving an entire column rather than a single value - is that the issue? I'm at a loss.

The closest existing post I've found is here - Power Query Applying a Function Across Every Column - though I don't think this solution is suitable for me due to the fact I'm wanting to apply a custom function rather than a hardcoded calculation.

Thanks in advance.

You need to supply sample data and expected output for people to help you. — davidebacci
– davidebacci, Commented Sep 28, 2023 at 15:47
As @DavideBacci writes, where is your sample data and expected output from that data? (at least for four or five columns). Please read How to create a Minimal, Complete, and Verifiable example. Then edit your question. Please provide the sample data as text after reviewing Markdown Tables Generator — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 28, 2023 at 19:15

horseyride · Accepted Answer · 2023-10-17 12:22:50Z

0

You can do it, but honestly the easiest thing to do overall is to click select the first two column, right click, unpivot other columns. Add new column ... custom column ... that contains the formula you want that references the first two columns and the new Value column. Delete the Value column. Click select attribute column, pivot using the new column as values

Sample

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Column1]*[Column2]*[Value]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom")
in  #"Pivoted Column"

Note if you really wanted to do a transform without pivoting, you could use

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Names  = List.RemoveFirstN(Table.ColumnNames(#"Source"),2),
Transform = Table.FromRecords(Table.TransformRows(Source, (x)=>Record.TransformFields(x, List.Transform( Names, (y) => { y, each _ * x[Column1] * x[Column2] } ))))
in Transform

edited Oct 17, 2023 at 12:22

answered Sep 28, 2023 at 16:21

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benedict Landauer Over a year ago

Thanks horseyride. Sorry for not including sample data and expected results as pointed out by other helpful users, but you managed to answer it anyway - you're right, simply unpivoting the data did the trick perfectly, there was no requirement to keep it in multiple columns, so thank you!

horseyride Over a year ago

I added the real answer to your original question if you want

Collectives™ on Stack Overflow

Power Query Custom Function Calculation Across Multiple Columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related