Repeat row answers in a new column

[Nicole_H]

Hi all,

I have a table of info on users, projects and their status.  I need a sort of master column that returns 'Closed' for every user ID if any of their projects are closed.  I have done it before in a 2 steps, where I extracted the value I wanted into a new column, then repeated against every ID in a second column.

Looking to do this in Power/M query

Thanks!

User ID	Project	Status	New Column 1	New Column 2
15123	One	Closed	Closed	Closed
15123	Two	Closed	Closed	Closed
246599	One	Closed	Closed	Closed
246599	Two	Open		Closed
331743	One	Closed	Closed	Closed
331743	Two	Open		Closed
425826	One	Open
425826	Two	Open
654736	One	Closed	Closed	Closed
654736	Two	Closed	Closed	Closed
713418	One	Closed	Closed	Closed
713418	Two	Open		Closed
872406	One	Open		Closed
872406	Two	Closed	Closed	Closed
905117	One	Open
905117	Two	Open
966066	One	Open
966066	Two	Open

[jgeddes]

Here is a solution that does not require merging.

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "bY87DsIwEAXv4jqF92/XHCANXZQOdyiJlILrg0BawN7GxRuPxl6WBAJIaUrz1l7n5b6f7ZbW6Quuj/0fIKvUGilOPs58tO29E4FxGHHSGYxSUN0Y9+6+ChtpVHAy/MOAGErkOOkqxZBz8Crfh0bNAmCj4XtXqKpZg4Lvv/fXJw==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Project = _t, Status = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"User ID", Int64.Type}, 
            {"Project", type text}, 
            {"Status", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"User ID"}, 
        {
            {"AllRows", each _, type table [User ID=nullable number, Project=nullable text, Status=nullable text]}
        }
    ),
    Custom1 = 
    Table.TransformColumns(
        #"Grouped Rows", 
        {
            {"AllRows", each Table.AddColumn(_, "ID_Status", (r)=> if List.Contains([Status], "Closed") then "Closed" else "Open", type text), type table}
        }
    ),
    #"Expanded AllRows" = 
    Table.ExpandTableColumn(
        Custom1, 
        "AllRows", 
        {"Project", "ID_Status"}, 
        {"Project", "ID_Status"}
    )
in
    #"Expanded AllRows"

This uses your sample data. To implement in your code, change the Source line to your Source.

[Nicole_H]

Thanks, I finally got this to work!

[Nicole_H]

Sorry, I missed the step about grouping by the ID, I'm not sure what to chose when I click on group by (I've never done this before)

[Nicole_H]

Thanks for your reply.  I've tried to do this, but I get the error:

Expression.Error: A cyclic reference was encountered during evaluation.

[v-mdharahman · Accepted solution]

Hi @Nicole_H,

Thanks for reaching out to the Microsoft fabric community forum.

You can achieve this in Power Query using grouping and merging logic. Here's a simple approach that should give you the desired result first load your table into Power Query then group the data by User ID and create a new column that checks if any of the statuses for that user are "Closed":

let
Source = YourTableName,
Grouped = Table.Group(Source, {"User ID"}, {
{"AnyClosed", each List.ContainsAny([Status], {"Closed"}), type logical}
})
in
Grouped

Now merge this grouped table back with your original table using User ID as the key and add a custom column to return "Closed" if AnyClosed is true, otherwise leave it blank (if [AnyClosed] then "Closed" else null). Remove helper columns if needed and rename your new column to Master Status or whatever suits your needs.

This method avoids manual steps and ensures that the "Closed" status is consistently applied across all rows for each user.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.