Refer to next row to perform calcuation in power Query

[yhong16]

 I am able to do in excel and calculate the logic I wanted but not in Power Query, can anyone help with it?

So when material number is null, grouping shall follow next row's grouping assignment.

When it has same text as next field, it shall be same as next row's assignment

If it is different than next row, it shall be "next row grouping - 1"

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlCK1SFEOjoiU05OyBSyOmdnOBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calculation.Material number" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Calculation.Material number]="" then null else [Calculation.Material number]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ",null,Replacer.ReplaceValue,{"Custom"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"Rows", each _, type table [Calculation.Material number=nullable text, Index=Int64.Type]}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Grouping", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"Calculation.Material number"}, {"Calculation.Material number"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Rows",{{"Grouping", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Custom"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[dufoq3 · Accepted solution]

Hi @yhong16, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlCK1SFEOjoiU05OyBSyOmdnOBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calculation.Material number" = _t]),
    // You can delete this step when applying to real data.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    Buffered = List.Buffer(ReplaceBlankToNull[Calculation.Material number]),
    Distinct = List.RemoveNulls(List.Distinct(Buffered)),
    LG = List.Generate(
        ()=> [ x = 0, y = List.Count(Distinct) ],
        each [x] < List.Count(Buffered),
        each [ x = [x]+1, y = if Buffered{[x]} = Buffered{x} then [y] else
                              if Buffered{[x]} = null then [y] else [y]-1 ],
        each [y]
    ),
    Combined = Table.FromColumns(Table.ToColumns(ReplaceBlankToNull) & {LG}, Value.Type(ReplaceBlankToNull & #table(type table[Expected Grouping=Int64.Type], {})))
in
    Combined

[yhong16]

Thank you so much! this works perfectly.

[Anonymous]

Hi @yhong16 ,

It is glad that the solutions help you solve the problem, you can accept the solution dufoq3  and lbendlin  offered as a solution so that more users can refer to.

Best Regards!

Yolo Zhu

[lbendlin]

You need to provide your own index column.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[yhong16]

This is the table. First column is my data, second is expected.

Calculation.Material number	Expected Grouping
	3
	3
	3
	3
	3
	3
AA	3
AA	3
BB	2
BB	2
	1
	1
CC	1
CC	1

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlCK1SFEOjoiU05OyBSyOmdnOBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calculation.Material number" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Calculation.Material number]="" then null else [Calculation.Material number]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ",null,Replacer.ReplaceValue,{"Custom"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"Custom"}, {{"Rows", each _, type table [Calculation.Material number=nullable text, Index=Int64.Type]}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Grouping", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"Calculation.Material number"}, {"Calculation.Material number"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Rows",{{"Grouping", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Custom"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.