Replace Index Column by others values

[Mederic]

Hello all,

I have an Index column as shown below (1) :

And I would like to replace the values by the following result in Power query (2) :

Thank you in advance

Best regards

(1)

Index
1
2
3
4
5
1
2
3
4
5

1
2
3
1
2
3

1
2
1
2

1
2
3
4
1
2
3
4

 (2)

Index
1
2
1
2
1
2
1
2
1
2

1
2
1
2
1
2

1
2
1
2

1
2
1
2
1
2
1
2

[Anonymous]

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.           

[Mederic]

Hello @Anonymous ,

I'm really sorry, I hadn't seen your reply and thank you for this alternative solution,
My problem is solved in another thread.

Have a nice evening

Best regards

[Mederic]

Hello,

This code seems to work,
If you have another solution, I'd be grateful

Best regards

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Index], each try if 
     Number.IsEven([Index]) then 2 else 1 otherwise null, Replacer.ReplaceValue,{"Index"})
in
    #"Replaced Value"