Forum Discussion

Diego11's avatar
Diego11
Frequent Visitor
9 months ago
Solved

Create custom column based on multiple rows

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

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    9 months ago

    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

     

     

4 Replies

  • Diego11's avatar
    Diego11
    Frequent Visitor
    9 months ago

    Hi,

     

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

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    9 months ago

    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
  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    9 months ago

    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

     

     

  • abdelazizallam0's avatar
    abdelazizallam0
    New Member
    9 months ago


    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"