Forum Discussion

Augusto_101's avatar
Augusto_101
Regular Visitor
2 years ago
Solved

Replace cell values by values in the same column

I need cell values in yellow to be replaced by values in cells in green. Can anyone help? Thank you

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Augusto_101 ,

    Go to the Transform tab and click on Transpose. This will transpose your table, swapping rows and columns. --> Add a index column --> Replace Values --> Remove the index column --> Transpose.
    All steps and the output:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMtQ30jcyMDIAMoHICMIzhPCMYbxYHbBaX38/F8dIiFxIqGswnBPu6uIH4YJU+helpBaBlOfn6bokVhZDmX6Z6RklIE5IaWoxTALMhsuEp6ZAJEDGGJoYmxuaQ52CQAgpE9xS5kCuhR6CNNKzhLPNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    Transpose = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(Transpose, "Index", 1, 1, Int64.Type),
    ReplaceValues = Table.ReplaceValue(#"Added Index", each [Column3], each if List.Contains({2,4,6},[Index]) then [Column1] else [Column3], Replacer.ReplaceValue,{"Column3"}),
    #"Removed Columns" = Table.RemoveColumns(ReplaceValues,{"Index"}),
    TransposeBack = Table.Transpose(#"Removed Columns")
in
    TransposeBack

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

5 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi Augusto_101 

    1. Remove first two rows

    2. Use first rows as Header

    3. Double click on header & Rename header as you want

     

    If solve your question, please hit the 👍  and mark as SOLUTION

    • Augusto_101's avatar
      Augusto_101
      Regular Visitor
      2 years ago

      Hi Pijush. Thank you for your reply but unfortunately that won't help because I need the date to be in the table so I can filter it in my report later on.

      • mlsx4's avatar
        mlsx4
        Memorable Member
        2 years ago

        Hi Augusto_101 

         

        You can do what PijushRoy has proposed and, once you have removed two first rows, then unfold the option of use first row as headers to select the other one: "Use headers as first row"

         

        Then you will get something like:

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Augusto_101 ,

    Go to the Transform tab and click on Transpose. This will transpose your table, swapping rows and columns. --> Add a index column --> Replace Values --> Remove the index column --> Transpose.
    All steps and the output:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMtQ30jcyMDIAMoHICMIzhPCMYbxYHbBaX38/F8dIiFxIqGswnBPu6uIH4YJU+helpBaBlOfn6bokVhZDmX6Z6RklIE5IaWoxTALMhsuEp6ZAJEDGGJoYmxuaQ52CQAgpE9xS5kCuhR6CNNKzhLPNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
    Transpose = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(Transpose, "Index", 1, 1, Int64.Type),
    ReplaceValues = Table.ReplaceValue(#"Added Index", each [Column3], each if List.Contains({2,4,6},[Index]) then [Column1] else [Column3], Replacer.ReplaceValue,{"Column3"}),
    #"Removed Columns" = Table.RemoveColumns(ReplaceValues,{"Index"}),
    TransposeBack = Table.Transpose(#"Removed Columns")
in
    TransposeBack

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Augusto_101 , add more column names if necessary

    let
    Source = your_table,
    col_names = {"Column8", "Column10", "Column12"},
    z = 
        Table.ReplaceRows(
            Source, 2, 1,
            {Record.RemoveFields(Source{2}, col_names) & Record.SelectFields(Source{0}, col_names)}
        )
in
    z