The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
How to make conditional column so that if category is "hand and face" the new category value is equal to LESC = Hand & Face's Last 52 Week value
(basically 13555264 but that number could change)
So in this case since all the categories are hand & face, the new category value column will all have 13555264 as their value
Solved! Go to Solution.
Hello - this works for me.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjMS1EAYbfE5FQlHSVHHx8FZw9HPz9Xn2AgF8hyUYgpNTAwMlNwc3R2BQoZGpuamhqZmSjF6pCsXcHZB2SCqYmpmYm5UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom = _t, SDESC = _t, LDESC = _t, #"$ Last 52 Weeks" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom", type text}, {"SDESC", type text}, {"LDESC", type text}, {"$ Last 52 Weeks", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NewColumn", let varLast52Weeks = List.PositionOf(#"Changed Type"[LDESC], "HAND & FACE") in each if [Custom] = "Hand and Face" then #"Changed Type"[#"$ Last 52 Weeks"]{varLast52Weeks} else null, type number)
in
#"Added Custom"
This is the error I am getting.
it also pushes some of my column headers down
how did you make the edits to the source and the changed type? i tried to invoke a custom column and add the
#"Added Custom" = Table.AddColumn(#"Changed Type", "NewColumn", let varLast52Weeks = List.PositionOf(#"Changed Type"[LDESC], "HAND & FACE") in each if [Custom] = "Hand and Face" then #"Changed Type"[#"$ Last 52 Weeks"]{varLast52Weeks} else null, type number) in
part shown, but i dont think it works without the rest of the given steps?
Hello - this works for me.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjMS1EAYbfE5FQlHSVHHx8FZw9HPz9Xn2AgF8hyUYgpNTAwMlNwc3R2BQoZGpuamhqZmSjF6pCsXcHZB2SCqYmpmYm5UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom = _t, SDESC = _t, LDESC = _t, #"$ Last 52 Weeks" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom", type text}, {"SDESC", type text}, {"LDESC", type text}, {"$ Last 52 Weeks", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NewColumn", let varLast52Weeks = List.PositionOf(#"Changed Type"[LDESC], "HAND & FACE") in each if [Custom] = "Hand and Face" then #"Changed Type"[#"$ Last 52 Weeks"]{varLast52Weeks} else null, type number)
in
#"Added Custom"
Dear @jennratten ,
I may not need your solution now but for sure in the future I will.
Thanks for sharing this short but effective custom column.
Ciao