Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Value | Result | Previous row Result + Current row Value |
-0,5 | 1 | Previous row is null so we take initial value '1' |
-0,5 | 0,5 | (1 + (- 0,5)) |
0,25 | 0,75 | (0,5 + 0,25) |
-0,5 | 0,5 | (0,75 + (-0,5)) |
Solved! Go to Solution.
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:
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.
yes you will find many examples of running totals. the arbitrary rule of starting with 1 is the only twist that I see.
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.
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |