Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.