Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost 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"...
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 🙂

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper 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
    Icon for Post Prodigy rankPost 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
      Icon for Super User rankSuper 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 🙂

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        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 🙂