Custom Column filled with a specific value from another column

[LorB]

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🙏

[LorB]

Amazing Thank you lots Testing it soon but seems to be working fine for you! You saved my life!

[jennratten]

You are very welcome!  If this met your needs, please go ahead and click Accept as Solution.  Thanks!

[jennratten · Accepted solution]

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
    NewColumn

Before

After