Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
1 year ago
Solved

function behavior

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. 





 

Need Help
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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 🙂

6 Replies

Replies have been turned off for this discussion
  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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's avatar
      Dicken
      Post Prodigy
      1 year ago

      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's avatar
        OwenAuger
        Super User
        1 year ago

        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 🙂

  • Dicken's avatar
    Dicken
    Post Prodigy
    1 year ago

    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