Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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!
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.
@ziv-mireia , where is column A and column B
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.
Hello? Anyone?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |