Forum Discussion
Custom Column filled with a specific value from another column
Hi Power Bi experts,
I am a bit desperate to find a solution and I've been searching in this community with no success So really hope someone can help me with this🙏
I need to add a custom column to be populated with a specific value from another column. So the new column to be filled with value from Column 1 row 3.
I am using Power query for this and tried record.field but with no success.
How can I achieve this? Also After this I will have to remove the first rows (3rd row including) because I need to unpivot the columns and create a dataset. After I fill the column with the value from column 1 row 3 and then remove Row 3, will the added custom column error?
Thank you lots in advance and I really hope with your help I can get to the bottom of this🙏
Hello - this is a way you can do it.
Script
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorVQdCm+kZm+kYGRkYwAef8nPwiICc4syoVLJCUU5oK5PuCOUWpKXB2eUZmCUjGRyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), TargetColumnName = "Column1", TargetRow = 2, // row indices begin at 0 // Alternative way to reference the target cell... // TargetValue = Source[Column1]{2} TargetValue = Table.Column(Source, TargetColumnName){TargetRow}, RemoveTopNRows = Table.Skip(Source,TargetRow+1), NewColumn = Table.AddColumn(RemoveTopNRows, "New Column", each TargetValue) in NewColumnBefore
After
3 Replies
Hello - this is a way you can do it.
Script
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorVQdCm+kZm+kYGRkYwAef8nPwiICc4syoVLJCUU5oK5PuCOUWpKXB2eUZmCUjGRyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), TargetColumnName = "Column1", TargetRow = 2, // row indices begin at 0 // Alternative way to reference the target cell... // TargetValue = Source[Column1]{2} TargetValue = Table.Column(Source, TargetColumnName){TargetRow}, RemoveTopNRows = Table.Skip(Source,TargetRow+1), NewColumn = Table.AddColumn(RemoveTopNRows, "New Column", each TargetValue) in NewColumnBefore
After
You are very welcome! If this met your needs, please go ahead and click Accept as Solution. Thanks!