Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Thank you in advance for the help! I have a table I am trying to fill in the nulls using the previous records value. Below is an example. The Value field has some missing values for multipe categories. The formula to fill in the missing values is simply to multiply the previous row's (within the category) Value number by the Growth Factor value. In this example the function would fill in (55 x 0.8) 44 for the first missing Value in Category A and 75.2 (94 x 0.8) for the first missing Value in Category B. The function would repeat untill all missing values are filled in.
Category | Growth Factor | Value |
A | 0.5 | 100 |
A | 1.1 | 110 |
A | 0.8 | 88 |
A | 0.9 | 79 |
A | 0.7 | 55 |
A | 0.8 | |
A | 0.9 | |
A | 0.4 | |
A | 0.6 | |
A | 0.7 | |
B | 0.5 | 200 |
B | 0.8 | 160 |
B | 1.2 | 192 |
B | 0.7 | 134 |
B | 0.7 | 94 |
B | 0.8 | |
B | 0.9 | |
B | 0.4 | |
B | 0.6 | |
B | 0.7 |
Solved! Go to Solution.
Does it have to be Power Query? In DAX you can get away with a ProductX function.
I'm still learning Power Query but here's how I would start approaching it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LCsAgDESvUrIuYmz8ZFmvId7/GlUqGmYz8B75TGv00k3exZHsPfX7N+x4Jh/jXRlZihE6MqsReWSMsHLBhmUBTsB5c901w6pZ9wdOx7ALMzWYmXmFHwGjAmfsIwUW4AS8ivYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Growth Factor" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Growth Factor", type number}, {"Value", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0
or [Category] <> #"Added Index"[Category]{[Index]-1} then [Value]
else [Growth Factor]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Value adj", each if [Index] = 0
or [Category] <> #"Added Index"[Category]{[Index]-1} then [Value]
else #"Added Custom1"[Custom]{[Index]-1}*[Growth Factor])
in
#"Added Custom"
This is not a complete solution yet, still needs the "productx" equivalent and grouping to make it work.
Hi @Anonymous
lbendlin ‘s reply has shown you the way by Power Query.
You could try my way to achieve your goal in Dax.
Firstly, we add an index column in Power Query Editor.
Then let’s add an other index column by calculated column.
Category ID =
var _rank=RANKX(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])),'Table'[Index],,ASC)
var _avgcountofP=DIVIDE(COUNTROWS('Table'),CALCULATE(DISTINCTCOUNT('Table'[Category]),ALL('Table')))
var _c=if(_rank>_avgcountofP,_avgcountofP,_rank)
return _c
Now let’s use a measure to fill value column.
Measure =
IF (
ISBLANK ( MAX ( 'Table'[Value] ) ),
PRODUCTX (
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Category ID] <= MAX ( 'Table'[Category ID] )
),
'Table'[Growth Factor]
)
* CALCULATE (
VALUES ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Category] = MAX ( 'Table'[Category] )
&& 'Table'[Category ID] = 1
)
) / 0.5,
MAX ( 'Table'[Value] )
)
Result:
You can download the pbix file form this link:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Does it have to be Power Query? In DAX you can get away with a ProductX function.
I'm still learning Power Query but here's how I would start approaching it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LCsAgDESvUrIuYmz8ZFmvId7/GlUqGmYz8B75TGv00k3exZHsPfX7N+x4Jh/jXRlZihE6MqsReWSMsHLBhmUBTsB5c901w6pZ9wdOx7ALMzWYmXmFHwGjAmfsIwUW4AS8ivYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Growth Factor" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Growth Factor", type number}, {"Value", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0
or [Category] <> #"Added Index"[Category]{[Index]-1} then [Value]
else [Growth Factor]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Value adj", each if [Index] = 0
or [Category] <> #"Added Index"[Category]{[Index]-1} then [Value]
else #"Added Custom1"[Custom]{[Index]-1}*[Growth Factor])
in
#"Added Custom"
This is not a complete solution yet, still needs the "productx" equivalent and grouping to make it work.
Here's a rather crude "solution" but it does what you need. As I understand in the equivalent of PRODUCTX() in Power Query is List.Product()
Power Query Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LCsAgDESvUrIuYmz8ZFmvId7/GlUqGmYz8B75TGv00k3exZHsPfX7N+x4Jh/jXRlZihE6MqsReWSMsHLBhmUBTsB5c901w6pZ9wdOx7ALMzWYmXmFHwGjAmfsIwUW4AS8ivYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Growth Factor" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Growth Factor", type number}, {"Value", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added IndexOffset" = Table.AddColumn(#"Added Index", "IndexOffset", each if [Index] = 0
or [Category] <> #"Added Index"[Category]{[Index]-1} then [Index]
else null),
#"Filled Down" = Table.FillDown(#"Added IndexOffset",{"IndexOffset"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each if [Index] = 0
or [Category] <> #"Filled Down"[Category]{[Index]-1} then [Value]
else [Growth Factor]),
#"Added Result" = Table.AddColumn(#"Added Custom", "Result", each List.Product(List.Range(#"Added Custom"[Custom],[IndexOffset],[Index]+1-[IndexOffset])))
in
#"Added Result"
Result:
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |