CODEWITHSUNDEEP
excelpowerbi
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

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

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.

Upvotes: 2

Views: 14358

Answers (1)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

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

Upvotes: 4

Related Questions