Duplicate value in one column with different categories in another column in Power Query

Question

If you observe the below table, Each asset is having multiple SourceTypes and the status of each SourceType was mentioned in "Status" Column.

Name	SourceType	Date	Status
Asset 1	Security	27-03-2024	Active
Asset 1	Application	24-03-2024	In-Active
Asset 1	System	27-03-2024	Active
Asset 2	System	27-03-2024	Active
Asset 2	Security	21-02-2024	In-Active
Asset 2	Application	15-06-2023	In-Active
Asset 3	System	27-03-2024	Active
Asset 3	Security	21-02-2024	Active
Asset 3	Application	15-06-2023	Active

I'm expecting the below output:

Name	SourceType	Date	Status
Asset 1	Security	27-03-2024	In-Active
Asset 1	Application	24-03-2024	In-Active
Asset 1	System	27-03-2024	In-Active
Asset 2	System	27-03-2024	In-Active
Asset 2	Security	21-02-2024	In-Active
Asset 2	Application	15-06-2023	In-Active
Asset 3	System	27-03-2024	Active
Asset 3	Security	21-02-2024	Active
Asset 3	Application	15-06-2023	Active

If any one SourceType is "In-Active", the other 2 should also be updated as "In-Active". It should be mentioned Active when all the 3 SourceTypes are Active.

I would be thankful if anyone can help me with this. Thank You:)

Answer 1

I suppose it depends on whether you are grouping just on Name, or Name & Date

For just Name

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"data", each 
    let base=_,     
    Inactivecount= List.Sum(List.Transform(base[Status], each if _="In-Active" then 1 else 0))>0,
    ActiveCount= Table.RowCount(base) = List.Sum(List.Transform(base[Status], each if _="Active" then 1 else 0)),
    New = Table.AddColumn(base,"Status2", each if ActiveCount then "Active" else if Inactivecount then "In-Active" else [Status]),
    #"Removed Columns" = Table.RemoveColumns(New,{"Status"})
    in #"Removed Columns", type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"SourceType", "Date", "Status2"}, {"SourceType", "Date", "Status"})
in  #"Expanded data"

for Name and Date

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name","Date"}, {{"data", each 
    let base=_,     
    Inactivecount= List.Sum(List.Transform(base[Status], each if _="In-Active" then 1 else 0))>0,
    ActiveCount= Table.RowCount(base) = List.Sum(List.Transform(base[Status], each if _="Active" then 1 else 0)),
    New = Table.AddColumn(base,"Status2", each if ActiveCount then "Active" else if Inactivecount then "In-Active" else [Status]),
     #"Removed Columns" = Table.RemoveColumns(New,{"Status"})
    in #"Removed Columns", type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"SourceType", "Status2"}, {"SourceType", "Status"})
in  #"Expanded data"

Answer 2

If I understand you correctly, I think you can just do a Table.ReplaceValue aggregation operation on each subgroup of Name

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Name", type text}, {"SourceType", type text}, {"Date", type date}, {"Status", type text}}),

//Group by name
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {

    //If any Status is "In-Active", then replace all with "In-Active" else don't change anything
        {"Transform", (t)=> if List.MatchesAny(t[Status],each _ = "In-Active") 
            then Table.ReplaceValue(t,each [Status], "In-Active",Replacer.ReplaceValue,{"Status"})
            else t, type table[Name=text, SourceType=text, Date=date, Status=text]
        }}),
        
    #"Expanded Transform" = Table.ExpandTableColumn(#"Grouped Rows", "Transform", {"SourceType", "Date", "Status"})
in
    #"Expanded Transform"