How to populate a column with specific value from cell in Excel query editor or Power BI query editor?

2
votes

My question concerns query editor of Excel and Power BI as well. How to get value from specific cell of the report and populate it as a new column with that value. Suppose we have a source with a flat file like this with a specific value in cell B2:

enter image description here

Which steps should be done to get desired results like this: enter image description here

So that new column is added to the table and the column is populated with a specific value of cell B2.

1
excelpowerbi

1 Answers

3
votes

In query editor, on Home tap, click Advanced Editor and paste the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUTIyMLTQNTDUNTQEcpRidaKVQDSU6VZUmllSDOQ55+fkF8FEEwsKckBai1JTYEIFOaW5QHZZZn5OaglMMDkjtaioEshLzkksggrHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column3"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    GetValueFromCell = #"Removed Columns"{0} [Column2],
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "NewColumnName", each GetValueFromCell)
in
    #"Added Custom"

The key step here is:

GetValueFromCell = #"Removed Columns"{0} [Column2],

where {0} means take value from first row of Column2, {1} would mean the second row and so on. 

#"Added Custom" = Table.AddColumn(#"Promoted Headers", "NewColumnName", each GetValueFromCell),

This post by Matt Allington was very helpful https://exceleratorbi.com.au/convert-a-cell-value-into-a-column-with-power-query