function behavior

[Dicken]

I have a question ragarding function behavior, I made a mistake and used table,column rathrer than record.field, 
so example table ; 

#table(
type table [ A = Text.Type , Unit = Any.Type] ,
{{"A", 2} , {"B", 3} , {"C", 4}} )

and then a function to add percentage column ; 

(atab as table , col as text )=>
let
Custom1 = Table.AddColumn( atab , "N%", (x)=>
Table.Column( x , "Unit") /
List.Sum( Table.Column( atab , "Unit") ) )
in
Custom1

now if you invoke the function on the table you get an error .
But if I add a column and then invoke on the same 'unit' column , still any.type it will work;

Table.AddColumn(
#table(
type table [ A = Text.Type , Unit = Any.Type] ,
{{"A", 2} , {"B", 3} , {"C", 4}} ),   "N",
each [Unit] )

can anyone explain this behavior, i only came acrross this by mistake as I should have used record.Field. in the first place. 

Richad. 

[Dicken]

Thanks, 
  as I said i came across by accident, I  did linkedin post giving this as an exampel ; 

let
  sourcetab = #table(type table [A = Any.Type, Unit = Any.Type], {{"a", 2}, {"b", 3}}),
  pfunc = (atab as table, acol as text) =>
    Table.AddColumn(atab, "N", each Table.Column(_, acol) / List.Sum(Table.Column(atab, acol))),
  result1 = pfunc(sourcetab, "Unit"),
  result2 = pfunc(Table.AddColumn(sourcetab, "New", (x) => 1), "Unit")
in
  result2

At least i can now accept it as 'one of those things'  and stop obsessing about it. 

Thanks, again, 
Richard

[OwenAuger]

Hi @Dicken 

In the function, change Table.Column to Record.Field.

The function provided as the 3rd argument of Table.AddColumn takes a single record as an argument, corresponding to each row of the table (1st argument). So, in this case, x takes on the value of records corresponding to each row of the table.

Also, did you intend col to be the column name rather than hard-coding "Unit"? If so, I would rewrite as:

(atab as table, col as text) =>
  let
    ColumnTotal  = List.Sum(Table.Column(atab, col)),
    AddPercent = Table.AddColumn(atab, "N%", (x) => Record.Field(x, col) / ColumnTotal)
  in
    AddPercent

I also added a step to compute total of the column separately before adding the percentage column, which may improve performance.

Does this work for you?

[Dicken]

Sorry but I still don't see why if I just add and extra column Table.Column will work, you have used 
Record.Field, which of course works,  but why does, Table.Column works if the table has 3 cols, but not 2 ? 
RD

[OwenAuger]

@Dicken I misread your original question 😅.

Yes, that is strange and after testing variations of your queries I can't explain it. The fact that Table.Column works in this specific situation looks like a bug.

It appears to require first adding a column (such as Unit) then adding a column using the "incorrect" function Table.Column.

I'll direct some others to look at this thread and see if they can provide an answer 🙂

[OwenAuger · Accepted solution]

Hi again @Dicken 

I did ask around some contacts. The general opinion is that it some sort of "leakage" of the internal representation of tables as either lists of records or records of lists. It shouldn't stricly behave this way but Power Query is erroneously allowing it in this case.

Another intriguing variation is this (credit to Greg Baldini )

let
  Source = #table(type table [Col1 = Text.Type, Col2 = Int64.Type], {{"A", 2}, {"B", 3}, {"C", 4}}),

  // this step yields an error for every row in "Col3" in the query preview
  AddColumn = Table.AddColumn(Source, "Col3", each Table.Column(_, "Col2"), Int64.Type),

  // this step does not yield an error, but crazier: it *undoes* the error in the prior step.
  // the preview here yields a "Col3" and a "NewCol" both of which have *no* errors.
  AddColumnWithTableColumn = Table.AddColumn(AddColumn, "NewCol", each Table.Column(_, "Col2"), Int64.Type)
in
  AddColumnWithTableColumn

I'll give some feedback to Microsoft when I have a chance. Feel free to do the same 🙂

[AlienSx]

Table.Group misbehaves

let
    Source = #table(type table [Col1 = Text.Type, Col2 = Int64.Type], {{"A", 2}, {"B", 3}, {"B", 4}}), 
    count_rows = Table.Group(Source, "Col1", {"count_rows", List.Count})
in
    count_rows