Forum Discussion
calculated column
how to achieve this in power bi column,In a table in power bi, I have 3 columns named as "parts" ,"code", "yes/no" are available.Based on parts -->codes are coming , one part will have multiple codes, if a part has "0" in the code then yes should come in the column(Yes/no) for that parts otherwise no sholud come.
Reference pic has attached
Hi dianate12
You can do this in Power Query or DAX.
Power Query:
Add a new column with the following code:
Table.SelectRows( Table.Group(#"Changed Type", {"Parts"}, {{"MinCode", each List.Min([code]), type nullable number}}) , (x) => x[Parts] = [Parts] )Expand the column and do a replace values with the following expression:
= Table.ReplaceValue(#"Changed Type1",each [#"Yes/No"], each if [#"Yes/No"] = "0" then "Yes" else "No",Replacer.ReplaceText,{"Yes/No"})Result below:
Full code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJQitWBsIzgLGMwKwkvKxmuAxsrBW5yCooYUG8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parts = _t, code = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parts", type text}, {"code", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "MinCode", each Table.SelectRows( Table.Group(#"Changed Type", {"Parts"}, {{"MinCode", each List.Min([code]), type nullable number}}) , (x) => x[Parts] = [Parts] )), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "MinCode", {"MinCode"}, {"Yes/No"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Yes/No", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",each [#"Yes/No"], each if [#"Yes/No"] = "0" then "Yes" else "No",Replacer.ReplaceText,{"Yes/No"}) in #"Replaced Value"DAX:
Yes / No DAX = VAR tt = 'Table'[Parts] RETURN IF ( MINX ( FILTER ( ALL ( 'Table'[Parts], 'Table'[code] ), 'Table'[Parts] = tt ), 'Table'[code] ) = 0, "Yes", "No" )
2 Replies
Hi dianate12
You can do this in Power Query or DAX.
Power Query:
Add a new column with the following code:
Table.SelectRows( Table.Group(#"Changed Type", {"Parts"}, {{"MinCode", each List.Min([code]), type nullable number}}) , (x) => x[Parts] = [Parts] )Expand the column and do a replace values with the following expression:
= Table.ReplaceValue(#"Changed Type1",each [#"Yes/No"], each if [#"Yes/No"] = "0" then "Yes" else "No",Replacer.ReplaceText,{"Yes/No"})Result below:
Full code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJQitWBsIzgLGMwKwkvKxmuAxsrBW5yCooYUG8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parts = _t, code = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parts", type text}, {"code", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "MinCode", each Table.SelectRows( Table.Group(#"Changed Type", {"Parts"}, {{"MinCode", each List.Min([code]), type nullable number}}) , (x) => x[Parts] = [Parts] )), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "MinCode", {"MinCode"}, {"Yes/No"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Yes/No", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",each [#"Yes/No"], each if [#"Yes/No"] = "0" then "Yes" else "No",Replacer.ReplaceText,{"Yes/No"}) in #"Replaced Value"DAX:
Yes / No DAX = VAR tt = 'Table'[Parts] RETURN IF ( MINX ( FILTER ( ALL ( 'Table'[Parts], 'Table'[code] ), 'Table'[Parts] = tt ), 'Table'[code] ) = 0, "Yes", "No" )Its working .
