Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |