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 “...
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

     

     

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident 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