Forum Discussion

Mederic's avatar
Mederic
Post Patron
3 years ago

Replace Index Column by others values

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

 

Need Help

3 Replies

  • Mederic's avatar
    Mederic
    Post Patron
    3 years ago

    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"

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Mederic
      Post Patron
      3 years ago

      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