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 have a table where I want to replace vaules in Collumn A if the vaule is greater than 480 and I want it to change to 480
The vaule in this collumn can not go over 480
In Power Query
Solved! Go to Solution.
hi @russellbruce ,
use add a custom column
type this in:
if [Collumn A] > 480 then 480 else [Collumn A]
click ok.
Let me know if this resolves the issue.
Appreciate a thumbs up if this is helpful.
Use this logic:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcvBDQAgCAPAXXj7QAsIsxD2X8MEfF1KSyYdXKqVdLe1ptKqe+uMyRjFogXz5Jid/B4xd9v8/5WqHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Data", Order.Descending}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Sorted Rows",
each if [Data] > 480 then [Data] else false,
each 480,
Replacer.ReplaceValue,
{"Data"}
)
in
#"Replaced Value"
If the value is over 480, then it returns the value of the column. If it isn't it evaluates to false and leaves the Table.ReplaceValues function.
If it returned the value of the Data field, it replaces it with 480 regardless of what it is (but your first condition said only return it if it is over 480)
So this:
Becomes this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse this logic:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcvBDQAgCAPAXXj7QAsIsxD2X8MEfF1KSyYdXKqVdLe1ptKqe+uMyRjFogXz5Jid/B4xd9v8/5WqHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Data", Order.Descending}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Sorted Rows",
each if [Data] > 480 then [Data] else false,
each 480,
Replacer.ReplaceValue,
{"Data"}
)
in
#"Replaced Value"
If the value is over 480, then it returns the value of the column. If it isn't it evaluates to false and leaves the Table.ReplaceValues function.
If it returned the value of the Data field, it replaces it with 480 regardless of what it is (but your first condition said only return it if it is over 480)
So this:
Becomes this:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinghi @russellbruce ,
use add a custom column
type this in:
if [Collumn A] > 480 then 480 else [Collumn A]
click ok.
Let me know if this resolves the issue.
Appreciate a thumbs up if this is helpful.
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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |