Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mp1977
Helper II
Helper II

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

 

Column1Column2Column3Column4Column5Column6Column7Column8Column9
nullnullnullGoal - SEP/20nullnullGoal - OCT/20nullnull
No.NameFunctionDaily goalWorked DayTotalDaily goalWorked DayTotal
1MaryA126261null0
2JohnA126261null0
3ClauB126261null0

 

What Iam trying to achieve

Column1Column2Column3Column4Column5Column6Column7Column8Column9
nullnullnullGoal - SEP/20nullnullGoal - OCT/20nullnull
No.NameFunctionDaily goalWorked DayGoal - SEP/20Daily goalWorked DayGoal - OCT/20
1MaryA126261null0
2JohnA126261null0
3ClauB126261null0


Any help is really appreciated
M.Penner

1 ACCEPTED SOLUTION
ziying35
Impactful Individual
Impactful Individual

@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

View solution in original post

4 REPLIES 4
ziying35
Impactful Individual
Impactful Individual

@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
Not applicable

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors