cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Conditional Column with variables

I want to compare the text strings in four columns. If the string is the same in all four columns, then the score is 6. If none of the strings match, then 0.

I can create six conditional columns for each of the pair combinations, and then add up the score.

I was hoping to do this in one step.

Something like this.

``````= Table.AddColumn(source, Score, each

vAB = if [A] = [B] then 1 else 0,
vAC = if [A] = [C] then 1 else 0,
vAD = if [A] = [D] then 1 else 0,
vBC = if [B] = [C] then 1 else 0,
vBD = if [B] = [D] then 1 else 0,
vCD = if [C] = [D] then 1 else 0,

vScore = vAB + vAC + vAD + vBC + vBD + vCD
)``````

I prefer to do this in Powerquery instead of DAX.

I am probably missing a simple syntax.

Thank you for any suggestions.

1 ACCEPTED SOLUTION
Super User

Whichever way you approach this, I think there will be a a few steps to the calculation.

However, to write it in a general way, I would do something like this:

``````= Table.AddColumn(
Source,
"Score",
each let
ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic
ValuesColumn = Table.FromList(ValuesList, each {_}),
GroupedCount =
Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count],
PairsCount =
List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2)))
in
PairsCount,
Int64.Type
)``````

There could be a smarter or less verbose approach - will give it some more thought 🙂

Regards

Owen Auger
Blog
3 REPLIES 3
Super User

Hi @MarkusEng1998, different approach here.

You have to define column names to calc here:

Result

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxOggRIzCGiRjAMUjECMpDqDHAqsYYiE2B2ARujjFUF1AkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
ColumnNamesToCalc = List.Buffer({"A", "B", "C", "D"}),
ColumnCombinations = List.Buffer(List.Accumulate(
List.Zip({ {0..List.Count(ColumnNamesToCalc)-1}, ColumnNamesToCalc }),
{},
(s,c)=> s & [ x = List.RemoveMatchingItems(List.Skip(ColumnNamesToCalc, c{0}+1), {c{1}}),
y = List.Repeat({c{1}}, List.Count(x)),
z = List.Zip({ y, x })
][z]
)),
ChangedType = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
List.Sum(List.Transform(ColumnCombinations, (x)=>
[ a = Record.FieldOrDefault(_, x{0}, null),
b = Record.FieldOrDefault(_, x{1}, null),
c = if a = b then 1 else 0
][c] )), type number)
in

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Super User

Whichever way you approach this, I think there will be a a few steps to the calculation.

However, to write it in a general way, I would do something like this:

``````= Table.AddColumn(
Source,
"Score",
each let
ValuesList = {[A],[B],[C],[D]}, // Could rewrite if you need it to be dynamic
ValuesColumn = Table.FromList(ValuesList, each {_}),
GroupedCount =
Table.Group(ValuesColumn,{"Column1"},{"Count", Table.RowCount, Int64.Type})[Count],
PairsCount =
List.Sum(List.Transform(GroupedCount, each Number.Combinations(_,2)))
in
PairsCount,
Int64.Type
)``````

There could be a smarter or less verbose approach - will give it some more thought 🙂

Regards

Owen Auger
Blog
Resolver II

Thank you @OwenAuger for the elegant solution!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors