Forum Discussion
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
| 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
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
4 Replies
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 resultHello 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
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 FinalCopy 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- 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-and-pivotable-functions-5d52daa996c9
