Looking for a coalesce function that doesn't calculate everything first RRS feed

  • Question

  • I want to coalesce (stop on the first non-null value, returned by) a set of functions. My first working case, looks something like this, however, because the functions are in an evaluated list, all 5 functions get called for every line of data in my table.

        Coalesce = (parameter1 as text, parameter2 as text) => List.First(List.RemoveNulls({
        #AddCoalescedColumn1 = Table.AddColumn(#"Previous Step","Coalesced1"each Coalesce([parameter1],[parameter2])

    So I wrote it again like this, which enforces the logic to only execute functions when the preceeding caller was not null, but it's really ugly code, and this is easy to do in C# with the ?? operator. See the last line of code, and each function call proceeds to call a function previously defined:

        Coalesce4 = (Value, parameter1, parameter2) =>
            if Value <> null 
            then Value 
            else Function4(parameter1, parameter2),
        Coalesce3 = (Value, parameter1, parameter2) =>
            if Value <> null 
            then Value 
            else Coalesce4(Function3(parameter1, parameter2), parameter1, parameter2),
        Coalesce2 = (Value, parameter1, parameter2) =>
            if Value <> null 
            then Value 
            else Coalesce3(Function2(parameter1, parameter2), parameter1, parameter2),
        Coalesce1 = (Value, parameter1, parameter2) =>
            if Value <> null 
            then Value 
            else Coalesce2(Function1(parameter1, parameter2), parameter1, parameter2),
        #AddCoalescedColumn2 = Table.AddColumn(#"Previous Step","Coalesced2"each Coalesce1(Function0([parameter1],[parameter2]),[parameter1],[parameter2]),

    Any other ideas on how to do this?

    Friday, November 22, 2019 7:33 PM

All replies

  • Hey nccontrol

    what about using a wrapper function that handels this for you? Something like this...

    (par1, par2) => 
        Result = if function1(par1, par2) <> null then
               function1(par1, par2)
               if function2(par1, par2) <> null then
               function2(par1, par2)
               if function3(par1, par2) <> null then
               function3(par1, par2)

    Query it

    Saturday, November 23, 2019 8:48 AM
  • Hi,

    In general, you should not be concern about that, thanks to lazy evaluation.

    This code works instantly:

    = List.First(List.RemoveNulls({null,1,null,VerySlowEvaluatedQuery, 5}))
    • Edited by Aleksei Zhigulin Saturday, November 23, 2019 10:47 PM
    • Proposed as answer by anthony34 Wednesday, November 27, 2019 3:18 PM
    Saturday, November 23, 2019 10:43 PM
  • A very good explanation about how it works by Ben Gribaudo:
    Power Query M Primer (Part 5): Paradigm

    Streaming Semantics

    Directly or indirectly, a M mashup is built by chaining methods. Call a function that fetches data from the source, pass its output to a function that applies a transformation, take the result of that function and pass it to another function that computes an aggregate and so forth.

    However, the just-described way of passing data can be inefficient. Implemented literally, it requires that the full output of the first method be assembled before being passed to the next method in the chain. Then, that method processes its entire input, doing whatever it does until it has produced a full set of output. This is then passed to the next method, and so forth. Each step of the chain can involve iterating through the entire data set, once per step.

    Why iterate through all the data once for each transform? Instead, why not iterate it once, feeding each item, as it’s encountered, through all the transform steps? Why not take the first item from the source, apply each transformation step to it in turn and then collect it as part of what eventually will be output by the expression as a whole, then repeat the process for the second item, and so forth?

    For lists and tables, M supports something like this automatically, without you having to do anything! Streaming semantics allows M to pull items from lists and tables through the chain of transforms. You simply code up M like you always have—by building the chain of methods that define the desired mashup—and M’s interpreter, when appropriate, streams items through that chain instead of executing each transformation step in its entirety on the entire data set before executing the next step.

    A moment ago, I mentioned pulling items through the chain of transforms. Why pull? Pulling allows the steps that needs data to control the amount of data that’s processed.

      Numbers = List.Generate(()=>1, each _ <= 100000000, each _ + 1),
      Filtered = List.Transform(Numbers, each _ * 2),
      Result = List.FirstN(Filtered, 3)

    Above, only the first three items are needed to produce the requested output. Since data is pulled as needed by the methods that needs it, List.FirstN causes only the first three items to flow through the method chain. Since only three items are pulled, List.Generate only generates the first three items. This is true even though List.Generate is coded to produce the sequence of numbers from 1 to 100,000,000. Whew! It would be wasteful to produce a 100,000,000 item list to have all but three of those items thrown away. Thankfully, with M’s streaming semantics, this didn’t happen.

    You don’t need to do anything to turn on streaming behavior. You simply assemble the processing steps in the order you want them. M takes care of streaming, when appropriate.

    M allows you to code up what should be done (the logic of the computations) without needing to define specifically how that what should be done (the flow of control). M, like other functional languages, is declarative, not imperative. You focus on declaring the intent; M takes care of choosing the technical steps to achieve that intent.

    Wednesday, November 27, 2019 3:23 PM