Query Editor: How to move a cell value to a separate cell the easiest way?

[ziv-mireia]

I am new to Power BI so please help me.

I want to move a single value from [KP20 rate] column to [Column2] column in the query editor, how can I achieve it in the simplest way?

I know I might need to declare a variable so please enlighten me. By the way, I will delete row 1 afterwards, promote my headers, and rename Column2 as YTD PERIOD.

Thank you,

[ConcreteMule]

Basically, you need to extract the content then insert it as a string in the ReplaceValue function.

See below.

let
Source = ...your source data.
#"Kept First Rows" = Table.FirstN(Source,1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"KP20 rate"}),
string_YTD = Text.From(#"Removed Other Columns"{0}[KP20 rate]),
Source2 = ...your source data
#"Replaced Value" = Table.ReplaceValue(Source2,"Total Hair Care", string_YTD, Replacer.ReplaceText,{"Column2"}),
#"Removed Top Rows" = Table.Skip(#"Replaced Value",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"null", "YTD PERIOD"}})
in
#"Renamed Columns"

You can also do the dynamic string for the "Total Hair Care" also by using the same method for getting string_YTD.

Hope this helps!

[ConcreteMule]

Basically you need to extract the content then insert into the ReplaceValue function

let
// These steps get the data needed to fullfill the replace.
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSUYoMcVEIyVcwstA3MNM3MgCKgCVideAqXPJzchKLihEsBfei/PKSDAVVhUhHsMKQ/JLEHAWPxMwiBefEolSgSnNzYzNzS3MzINPQQM/I1MLc3MLQVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t, #"KP20 rate" = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}, {"KP20 rate", type text}, {"Column3", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"KP20 rate"}),
string_YTD = Text.From(#"Removed Other Columns"{0}[KP20 rate]),
// These steps use the previous output in string_YTD to use in the replace function.
Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSUYoMcVEIyVcwstA3MNM3MgCKgCVideAqXPJzchKLihEsBfei/PKSDAVVhUhHsMKQ/JLEHAWPxMwiBefEolSgSnNzYzNzS3MzINPQQM/I1MLc3MLQVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t, #"KP20 rate" = _t, Column3 = _t]),
// The replace function with the added output string_YTD
#"Replaced Value" = Table.ReplaceValue(Source2,"Total Hair Care",//string to replace this can be dynamic also if it changes using similar method above.
string_YTD, //the string used from string_YTD
Replacer.ReplaceText,{"Column2"}), //End of ReplaceValue.
// Next steps are to finish out the rest of you datamodel.
#"Removed Top Rows" = Table.Skip(#"Replaced Value",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"null", type text}, {"Dollars", Int64.Type}, {"Dollars Growth % YA", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"null", "YTD PERIOD"}})
in
#"Renamed Columns"

Hope this helps.

[amitchandak]

@ziv-mireia , where is column A and column B

[ziv-mireia]

I have a screenshot on my post. Column A refers to the first column, and column B refers to the second column.

If you want me to be explicit, I want to move a cell value from KP20 rate to Column2.

[ziv-mireia]

Hello? Anyone?

[v-rzhou-msft]

Hi @ziv-mireia 

If you want to replace “Total Hair Care” in Column2, you can rightclick the value you want to replace and input the new value into it.

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[ziv-mireia]

Hi, I am familiar with the replace value function but it will not solve the problem. The solution should be dynamic as come next month and the following months the "YTD to 28/06/20" will be something else.

I am actually looking for a cell.value (like in VBA) solution within the query editor in Power BI. I know it should be an M language solution.