Replace Index Column by others values

Hi Mederic ，

Here's my solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkn8sjiUYIpgCCOz8VqEVTwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=null then [Index.1] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Up",null,1,Replacer.ReplaceValue,{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Custom"}, {{"All Rows", each _, type table [Index=nullable number, Index.1=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([All Rows],"GroupIndex",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All Rows"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Index", "Index.1", "GroupIndex"}, {"Index", "Index.1", "GroupIndex"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [Index]=null then null else if Number.Mod([GroupIndex],2)=0 then 2 else 1)
in
    #"Added Custom2"

You can download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.