Converting whole numbers to decimals with row-wise division

[CCHarrison]

Hello,

I am failing to achieve a transform that seems like it should be easy. I would greatly appreciate some insight into how to make this work.
I am trying to write a function to convert tables like this

Region	State	Total	group1	group2	group3
A	x	21546	2127	5485	5784
B	x	4526	441	2399	158
C	y	45873	21501	7845	8856

(i.e. with 2 leading text columns, a numeric "Total" column, and a variable number of numeric value columns (3 shown: group1, group2, group3)

to this:

Region	State	group1	group2	group3
A	x	0.098	0.255	0.268
B	x	0.097	0.530	0.035
C	y	0.469	0.171	0.193

i.e.

• ignore the text columns
• divide each value in the group columns by the corresponding Total value
• delete the Total column

The function needs to be able to convert any such table, regardless of how many "group" columns it has. I know how to step around the initial text columns. The part that is stumping me is how to write the division operation transform.
I have been trying this kind of thing, but I can't make it work:

        TransformedRows =
            Table.FromRecords(
                List.Transform(
                    Table.ToRecords(Tbl),
                    (row) =>
                        Record.TransformFields(
                            row,
                            List.Transform(
                                ColumnsToDivide,
                                (col) => {
                                    col,
                                    try row[col] / row[Total] otherwise null
                                }
                            )
                        )
                )
            )

I keep getting 'Expression.Error: Expected a TransformOperations value. Details: [List]', which I really don't understand.

If someone can show me what I'm doing wrong here, I would be most grateful.
Alternatively, if there's a different/better way to approach this kind of thing, I am all ears!

Many thanks.

[AlienSx · Accepted solution]

Table.FromList(
    Table.ToList(
        Tbl, 
        (x) => List.FirstN(x, 2) & 
            List.Transform(
                List.Skip(x, 3), 
                (z) => if x{2} = 0 then null else z / x{2}
            )
    ),
    each _,
    List.RemoveRange(Table.ColumnNames(Tbl), 2, 1)
)

[CCHarrison]

Hey AlienSX, just a curiosity... I thought Table.ToList() would object to any non-text values unless you gave it an explicit type conversion argument of some kind. But your code works when my input Tbl has Int64 or decimal types in the conversion columns. I don't understand which element of your solution is getting around this type restriction.  

[AlienSx]

Hello, @CCHarrison there is no type restriction when you explicitly define "combiner" function as 2nd argument. This argument (optional combiner as nullable function) is optional. If you don't use it (or use null) then Table.ToList applies default Combiner.CombineTextByDelimiter(",") which in turn requires a list of values of type text.

But when you define your own "combiner" function then it's all yours. This "combiner" function must be a function of single argument. Table.ToList will pass the list of row values to your function. Try simplest (x) => x and see the result.

[CCHarrison]

Excellent. Many thanks.

[OwenAuger]

Hi @CCHarrison 

The issue with the existing code snippet is that the 2nd item in the list within curly braces should itself be a function that is applied to transform each value of the fields whose names are listed in ColumnsToDivide.

There is also a separate problem that row[col] is not a valid field reference (in this case) since it refers to the field of record row with literal name "col" . If you did need to refer to this field, you could use Record.Field(row, col), however it turns out we don't need this field reference anyway.

Idea 1

Cutting to the chase, a quick fix would be to change this:

(col) => { col, try row[col] / row[Total] otherwise null } 

to this:

(col) => { col, (value) => try value / row[Total] otherwise null } 

or equivalently:

(col) => { col, each try _ / row[Total] otherwise null } 

Idea 2

You could also simplify the code a little using Table.TransformRows rather than List.Transform( Table.ToRecords(...)) along with some minor tweaks:

  TransformedRows = Table.FromRecords(
    Table.TransformRows(
      Tbl, 
      (row) =>
        let
          RowTransform = List.Transform(
            ColumnsToDivide, 
            (col) => {col, (value) => try value / row[Total] otherwise null}
          ), 
          DivideColumns = Record.TransformFields(row, RowTransform)
        in
          DivideColumns
    )
  )

Idea 3

Use List.Accumulate to iterate through the columns and use Table.ReplaceValue to update the required columns:

TransformedRows = List.Accumulate(
    ColumnsToDivide, 
    Tbl, 
    (CurrentTbl, CurrentCol) =>
      Table.ReplaceValue(
        CurrentTbl, 
        each Record.Field(_, CurrentCol), 
        (row) as number => try Record.Field(row, CurrentCol) / row[Total] otherwise null, 
        Replacer.ReplaceValue, 
        {CurrentCol}
      )
  )

I haven't worried about fixing column types (in Ideas 1 & 2) or removing the Total column but I assume you're handling that.

Does the above help at all?

[CCHarrison]

Thank you for both the explanation and the solution(s).