Create custom column based on multiple rows

[Diego11]

Hi everyone,

I have a table with a list of clients, and each of them may have multiple presenting issues (Housing, Mental Health, Domestic Violence, Work-related). I need to create a column called “Type of issue” that indicates whether each client has work-related issues, personal issues (any issues that are not work-related), or both (personal and work-related).

Here is an example of what I have:

User ID	Presenting Issue
1	Housing
1	Mental Health
2	Work related
2	Mental Health
2	Domestic Violence
3	Work related
4	Mental Health
4	Work related
4	Housing

And this is what I need as a result

User Id	Type of issue
1	Personal Issue
2	Both issues
3	Work related issue
4	Both issues

How can I create this “type of issue” column based on multiple rows?

Thank you so much in advance

[Diego11]

Hi,

Thanks to everyone for your help 🙂 I got the solution. 

[abdelazizallam0]

let
Source = Table.NestedJoin(Table1, {"User ID"}, Table2, {"User Id  "}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Type of issue"}, {"Type of issue"})
in
#"Expanded Table2"

[PhilipTreacy · Accepted solution]

Hi @Diego11 

Download example PBIX file with the code below

If you want a solution that might be more flexible in the future and allow you to add more types of issues including adding "sub-types" of work related issues (not just "Work related") you could try this.

The idea is that you assign each type of issue a value:

1 Work related

2 Housing, Mental Health, Domestic Violence

3 [Can be used in the future]

You end up with a column like this 

By your criteria you are only concerned with the type of issue so you can just use distinct values in the Issue Value column

All work related issues = 1

All personal issues = 2

You can sum these distinct values.

If the sum is 1 then they only have work related issues.

If the sum is 2 then they only have personal issues.

If the sum is 3 then they have both issues.

Regards

Phil

[m_dekorte]

Hi @Diego11,

Give this a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLILy3OzEtXitWB8H1T80oScxQ8UhNzSjLAokZA0fD8omyFotScxJLUFLggdqUu+bmpxSWZyQphmfk5qXnJqWAZY2yGmGA1xASXUrhTYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Presenting Issue" = _t]),
    GroupedRows = Table.Group(Source,{"User ID"},
        {
            {
                "Type of issue", each
                    let
                        issues = List.Distinct([Presenting Issue]),
                        hasWork = List.Contains(issues, "Work related"),
                        hasPersonal = not List.IsEmpty(List.RemoveItems(issues, {"Work related"}))
                    in
                        if hasWork and hasPersonal then "Both issues"
                        else if hasWork then "Work related issue"
                        else "Personal issue", type text
            }
        }
    )
in
    GroupedRows