cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
art3c
New Member

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
-0,5

 

My goal is to get previous row result and add to it value from current row with rule: if result < 0,5 then result= 0,5 and if result> 3 then result = 3.

So the final table should be:

 

ValueResultPrevious row Result + Current row Value
-0,51Previous row is null so we take initial value '1'
-0,50,5(1 + (- 0,5))
0,250,75(0,5 + 0,25)
-0,50,5(0,75 + (-0,5))
1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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:

Result from List.Accumulate.png

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
swimming123
Regular Visitor

Hi there,

 

I just want to do a very simple loop a list and each of loop add a column to table; after many seacrh and test , still don't know how to do it. Could you give a simple example? Thank you very much.

CahabaData
Memorable Member
Memorable Member

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

www.CahabaData.com

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
Community Champion
Community Champion

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:

Result from List.Accumulate.png

Specializing in Power Query Formula Language (M)

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.

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!