Forum Discussion

art3c's avatar
art3c
New Member
9 years ago
Solved

Recursive query

Hello,   It is possible to execute recursive query (something similar to SQL CTE recursive)? I have table with following data (there is no limit of data rows count):     -0,5 -0,5 0,25 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Piece of cake for Power Query's List.Accumulate:

     

    let
    Source = Table1,
    ResultLists = List.Accumulate(List.Skip(Source[Value]),{1},(Result,Value) => Result & {List.Min({3,List.Max({0.5,List.Last(Result) + Value})})}),
    TableFromColumns = Table.FromColumns({Source[Value],ResultLists},type table[Value = number, Result = number])
in
    TableFromColumns

     

    I share the doubt regarding the first result: according to the information provided, it should be 1, regardless of the first value.

    I would expect some initial value (e.g. 1.5) and the first result would be that initial value plus the first value (1.5 - 0.5 = 1), not lower than 0.5 and not higher than 3.0.

    Anyhow the code is based on the information provided, so the first value is skipped.

     

    A screenshot with the result from a larger sample:

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
9 years ago

yes you will find many examples of running totals.  the arbitrary rule of starting with 1 is the only twist that I see.

art3c's avatar
art3c
New Member
9 years ago

It's not that easy I think. I need to calculate value of Result column based on previous row Result column value. That's why recursion could be proper solution, but I dont't know if it's possible with Power BI.

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Piece of cake for Power Query's List.Accumulate:

     

    let
    Source = Table1,
    ResultLists = List.Accumulate(List.Skip(Source[Value]),{1},(Result,Value) => Result & {List.Min({3,List.Max({0.5,List.Last(Result) + Value})})}),
    TableFromColumns = Table.FromColumns({Source[Value],ResultLists},type table[Value = number, Result = number])
in
    TableFromColumns

     

    I share the doubt regarding the first result: according to the information provided, it should be 1, regardless of the first value.

    I would expect some initial value (e.g. 1.5) and the first result would be that initial value plus the first value (1.5 - 0.5 = 1), not lower than 0.5 and not higher than 3.0.

    Anyhow the code is based on the information provided, so the first value is skipped.

     

    A screenshot with the result from a larger sample:

    • art3c's avatar
      art3c
      New Member
      9 years ago

      Wow, Power BI has so great community! Thank you for your solution.

    • swimming123's avatar
      swimming123
      Regular Visitor
      8 years ago

      Hi there,

       

      Is there a way to loop a list, each of the loop I will insert a new column name and calculate values. I did many search and test, still not success. Could you tell me a simple example? Thank you.