none
List.Transform(Table.ToRecords(t), each complexFunc([field])) vs Table.AddColumn(t, "tmp", each complexFunc([field]))[tmp] RRS feed

  • Question

  • What is better?

    Please understand my situation - the complexFunc is so long, that I cannot afford "try" approach.

    At the same time I saw huge number of cases, then simplified example doesn't provide the same results as very complex one.

    So for me the only possibility to make good decision is to ask you guys what is better by design, or both approaches are just equal.

    Thanks for your understanding!

    Monday, July 29, 2019 1:45 PM

Answers

  • In the absence of any other transformations, I would expect these two approaches to be roughly equivalent. But keeping things in "table space" will potentially let us reorder operations. For instance, in this query

    let
        t = #table({"A", "B", "C"}, {{1, "2", "3"}, {2, "a", "b"}}),
        a = Table.AddColumn(t, "D", each Number.FromText([B])),
        r = Table.SelectRows(a, each [A] <> 2)
    in
        r

    we reorder the Table.SelectRows to come before Table.AddColumn as it's more efficient to reduce the size of the data set before adding the column. Using Table.FromRecords would prevent that kind of optimization.

    Wednesday, August 7, 2019 8:22 PM

All replies

  • I would expect them to be roughly equivalent, unless complexFunc can be folded back to the original data source (in which case Table.AddColumn would likely be faster). But you'd have to measure to be sure.

    Ehren

    Tuesday, August 6, 2019 5:56 PM
    Owner
  • In the absence of any other transformations, I would expect these two approaches to be roughly equivalent. But keeping things in "table space" will potentially let us reorder operations. For instance, in this query

    let
        t = #table({"A", "B", "C"}, {{1, "2", "3"}, {2, "a", "b"}}),
        a = Table.AddColumn(t, "D", each Number.FromText([B])),
        r = Table.SelectRows(a, each [A] <> 2)
    in
        r

    we reorder the Table.SelectRows to come before Table.AddColumn as it's more efficient to reduce the size of the data set before adding the column. Using Table.FromRecords would prevent that kind of optimization.

    Wednesday, August 7, 2019 8:22 PM
  • Thanks for this valuable tip, Curt!

    @Andrey: Hard to imagine why this answer doesn't fit to your question, so I'm accepting it as answer. But feel free to specify why you think different.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, August 16, 2019 6:04 PM
    Moderator