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
Anonymous
Not applicable

Power Query: Use Previous Rows Value in Current Calculation

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. 

 

CategoryGrowth FactorValue
A0.5100
A1.1110
A0.888
A0.979
A0.755
A0.8 
A0.9 
A0.4 
A0.6 
A0.7 
B0.5200
B0.8160
B1.2192
B0.7134
B0.794
B0.8 
B0.9 
B0.4 
B0.6 
B0.7 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

1.png

Then lets 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 lets 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:

2.png

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EQUN7KKiMIFLp_qopGUL9jMBAWb2JMAG2M8ZW0SMX6Zz8g?e=XscVth

 

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. 

lbendlin
Super User
Super User

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:

lbendlin_0-1595469685081.png

 

 

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.