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.
Hi.
I am still a complete beginner in Power BI / Query and have not yet found what I am looking for using the search function.
I would like to insert a new column in which the sum of Answer 1 to Answer 3 is calculated if it contains a number greater than 3.
Can anyone help me here?
Thank you!
Solved! Go to Solution.
Hi @chrkrg,
If your actual data has the same set up as depicted above, try something like this.
let
Source = Table.FromColumns(
{
{1..9},
{1..3}&{1..3}&{1..3},
{4..6}&{2..4}&{1..3},
{2..4}&{1..3}&{4..6}
}, {"Participant", "Answer 1", "Answer 2", "Answer 3"}
),
AddSumAbove3 = Table.AddColumn(Source, "Custom", each List.Sum( List.Select( List.Skip(Record.ToList(_), 1), each _ >3 )))
in
AddSumAbove3
When you have other columns as well but they're placed adjacent to each other, try this:
List.Sum( List.Select( List.Range( Record.ToList(_), 1, 3), each _ >3 )))
Or when you need to look up "Answer" fields, try this:
List.Sum( List.Select( Record.ToList( Record.SelectFields(_, List.Select( Record.FieldNames(_), each Text.StartsWith( _, "Answer", Comparer.OrdinalIgnoreCase)))), each _ >3 ))
Ps. If this helps solve your query please mark this post as Solution, thanks!
Hi @chrkrg,
If your actual data has the same set up as depicted above, try something like this.
let
Source = Table.FromColumns(
{
{1..9},
{1..3}&{1..3}&{1..3},
{4..6}&{2..4}&{1..3},
{2..4}&{1..3}&{4..6}
}, {"Participant", "Answer 1", "Answer 2", "Answer 3"}
),
AddSumAbove3 = Table.AddColumn(Source, "Custom", each List.Sum( List.Select( List.Skip(Record.ToList(_), 1), each _ >3 )))
in
AddSumAbove3
When you have other columns as well but they're placed adjacent to each other, try this:
List.Sum( List.Select( List.Range( Record.ToList(_), 1, 3), each _ >3 )))
Or when you need to look up "Answer" fields, try this:
List.Sum( List.Select( Record.ToList( Record.SelectFields(_, List.Select( Record.FieldNames(_), each Text.StartsWith( _, "Answer", Comparer.OrdinalIgnoreCase)))), each _ >3 ))
Ps. If this helps solve your query please mark this post as Solution, thanks!
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.