Replace value with a value from other column

[Mp1977]

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

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
null	null	null	Goal - SEP/20	null	null	Goal - OCT/20	null	null
No.	Name	Function	Daily goal	Worked Day	Total	Daily goal	Worked Day	Total
1	Mary	A	1	26	26	1	null	0
2	John	A	1	26	26	1	null	0
3	Clau	B	1	26	26	1	null	0

What Iam trying to achieve

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
null	null	null	Goal - SEP/20	null	null	Goal - OCT/20	null	null
No.	Name	Function	Daily goal	Worked Day	Goal - SEP/20	Daily goal	Worked Day	Goal - OCT/20
1	Mary	A	1	26	26	1	null	0
2	John	A	1	26	26	1	null	0
3	Clau	B	1	26	26	1	null	0

Any help is really appreciated
M.Penner

[ziying35 · Accepted solution]

@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

[Anonymous]

This type of report should be created in visuals in Power BI. The format of the table is wrong for data modeling as you'll soon discover. You should avoid such unwieldy constructs since they will give you a lot of grief and pain. Instead, you should work in agreement with the tools, not against them. The fact that something is doable, does not always mean it should be done. Judging by what you're trying to do, I think you should not do it. You should rather create a tidy table that's suitable for DAX calculations. Please read about tidy data: https://towardsdatascience.com/whats-tidy-data-how-to-organize-messy-datasets-in-python-with-melt-an...

[Jimmy801]

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

[Jimmy801]

Hello @Mp1977 

do you have access to the file where this pivot-table is in? I always would reccomend you to change your database as possible and then read it as new database. You could put your goal-columns into rows and then read it. Or what is the goal of adapting a visual database into another visual database?

BR

Jimmy