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 with two columns Attribute and Value
Attribute | Value |
A | 3000 |
B | null |
I would like to replace the null value with 30% of the corresponding value of A.
I do not want to pivot the information , as it is a lot of data and tranformations already running in the query.
I am looking for a solution/logic to calculate at a row level.
Thanks.
Solved! Go to Solution.
your example is really minimalist (at the very least, to get a meaningful answer, you should better explain what kind of "correspondence" there is between A and B. ).
In any case, the attached proposal works for a very large database and on the assumption that the nulls (which can be even more than 1) to be filled with the percentages are all consecutive and following the number available
Hi @VSrujana ,
If the table just like your shows, you can try this query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MDBQitWJVnICcpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New", each if [Value] = null then List.Max(#"Changed Type"[Value]) * 0.3 else [Value]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"New", Int64.Type}})
in
#"Changed Type1"
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
your example is really minimalist (at the very least, to get a meaningful answer, you should better explain what kind of "correspondence" there is between A and B. ).
In any case, the attached proposal works for a very large database and on the assumption that the nulls (which can be even more than 1) to be filled with the percentages are all consecutive and following the number available
It isn't clear if you will alway have a number followed by a null (not multiple nulls). If not, this is one way to do it. If so, please provide more data/description. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MDBQitWJVnICcpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "ReplacedWith30Pct", each if [Value] = null then 0.3 * #"Added Index"{[Index]-1}[Value] else [Value], type number)
in
#"Added Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.