Forum Discussion

Mp1977's avatar
Mp1977
Icon for Helper II rankHelper II
6 years ago
Solved

Replace value with a value from other column

Hi guys, can someone help me out with the following problem? I have a Excel table that has the month valued merged for 3 columns and I need it in the Total Cell. What I Have in power query ...
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Mp1977 

    Try this:

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("vZA9C8IwFEX/SshctU21H27aqiBYBQsO6hC0aDFNoDRDEf+7HbR9L3QUx9zcyzm845NGSuhCOnRKpRaCWp+AmYFrBuMmWCkuyIDsF7vTiNnt18Tsembgd+NtlKJxYHbDb/CyoG6ihsg24UWGbJdaXqpcSWQc81zU5Nagke1BlY/sSmJeI+dUVRxL9+yD/n3Y7pG3A503vKyR8wzKOsCQecCre/ig1Xc4G7EZZK/VXf6R7UJ2JLhG7PnP2ec3", BinaryEncoding.Base64),Compression.Deflate))),
    transp = Table.Transpose(Source),
    group = Table.Group(
               transp, 
               "Column1", 
               {
                   "t", 
                   (tbl)=> Table.ReplaceValue(
                                tbl, 
                                each tbl[Column1]{0}, 
                                each [Column2]="Total", 
                                (x,y,z)=>if z then y else x, 
                                {"Column2"}
                            )
                },
                0,
                (x,y)=>Byte.From(Text.StartsWith(y, "Goal"))
            ),
    result = Table.Transpose(Table.Combine(group[t]))
in
    result
Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Mp1977 

 

here some approach how to solve it.

Extract the first row as record and transform it to a table. Use only the filled value fields. Transform the column names by adding 2 to the number of the column. Join this record to you 2nd row and append the rest of your data table. Then promote the headers to have a good looking tabel 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQaPc8xNzFHQVgl0D9I0McMn6O4dgysbqRCv55esBuX6JualAyq00L7kkMz8PyHRJzMypVEgH6gZywvOLslNTFFwSK4GckPwSsCAhFSDTDYE838QikKAjEIO4RmYwwhDhHAOwaiMgyys/I4841cZAlnNOYimQciKgOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    First= Record.ToTable( Table.First(#"Changed Type")),
    #"Filtered Rows" = Table.SelectRows(First, each ([Value] <> "null")),
    TransName = Table.TransformColumns
    (
        #"Filtered Rows",
        {
            {
                "Name",
                each "Column" & Text.From(Number.From(Text.Replace(_, "Column", ""))+2)
            }
        }
    ),
    TableRecord = #"Changed Type"{1} & Record.FromTable(TransName), 
    Final = Table.PromoteHeaders(Table.Combine({Table.FromRecords({TableRecord}), Table.Range(#"Changed Type",2)}))
    
in
    Final

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy