Forum Discussion
Repeat row answers in a new column
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
GroupedNow 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.
Super UserHere 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.
Thanks, I finally got this to work!