Group & generate a list

[Anonymous]

The table below shows whether zones & Cat for a given area are open or closed.

I want to build a query that looks for (zones - Cat) together and decided the latest open condition & list them as the output table.

[ronrsnfld · Accepted solution]

The following code should handle various areas without any hard-coding.

Please read the code comments to better understand the algorithm:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="EventTbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}, {"Zone", type text}, {"Date", type date}, {"EVENTS", type text}, {"Hall", type text}}),
    
//Group by Area, Zone and Hall
// Then determine EVENT status for each most recent Date
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Area", "Zone", "Hall"}, {
        {"Status", (t)=>Table.Max(t, each [Date])[EVENTS]
        , type text}}),

//Group by Area
// then determine Open and All Zones
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Area"}, {
        {"Active Zones", (t)=>Text.Combine(List.Sort(List.Distinct(Table.SelectRows(t, each [Status]="Open")[Zone])),","), type text},
        {"All Zones", (t)=>Text.Combine(List.Sort(List.Distinct(t[Zone])),", "), type text}})
in 
    #"Grouped Rows1"

I combined your two tables to show results with two different areas. Also note that I changed the column headers for your first example to match what you showed in your second example. Depending on your actual data, you may need to make some column name changes in the code.

Source Data

Results

[Mahesh0016]

@Anonymous I hope this helps you. Thank You.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZA9C8MwDET/i+coPclWoGOS7l0LIVuyhbbQ/w89Q78ItcEHPng8CU1T6EWHS2gC9MBnUGc5MQqIAfyN2+2xLmFu/tBHloExSKyw3S8bISmz5/t6LVtHJkG6ijWRNbw3cIlesn7IbPWXtTjfbOesXMAiS5+trYt+tfMT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ares = _t, Date = _t, Zone = _t, Cat = _t, Conditional = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ares", type text}, {"Date", type date}, {"Zone", type text}, {"Cat", type text}, {"Conditional", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ares"}, {{"All Zone", each Text.Combine(List.Distinct(_[Zone]),","), type table [Ares=nullable text, Date=nullable date, Zone=nullable text, Cat=nullable text, Conditional=nullable text]}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Conditional] = "Open")),
#"Grouped Row" = Table.Group(#"Filtered Rows", {"Ares"}, {{"Active Zone", each Text.Combine(List.Distinct(_[Zone]),","), type table [Ares=nullable text, Date=nullable date, Zone=nullable text, Cat=nullable text, Conditional=nullable text]}}),
#"MergedTables" = Table.Join(
#"Grouped Row",
"Ares",
#"Grouped Rows",
"Ares",
JoinKind.Inner
)
in
#"MergedTables"

[Anonymous]

Mahesh0016

Thanks for your efforts. however, the output table is not what I need, probably I have not explained it very well.

The below snapshot did.

I don't need to filter out the open zones only, but any zone that has open and closed record for the same Hall should cancel each other, so I left with zone D that has open record only.

The other thing that when query is loaded to excel it shows only one coloumn which is the Area only!

[v-cgao-msft]

Hi @Anonymous ，

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdG7CsMwDAXQf/EcYUlO/Bj7oGvWQsjWbKUt9P+hNjZRnEcHgzEHX100DOoE9xuQatQ5HkJNmkLAeO8/0ys9AauxKY6PnYFWnCnOZ2aFdWCFtVt2eb6/02Od2xXoNFvNSLiUVbLdkSXagRfn0g85Ojk2i8YIjCK9DDnLObuqE/7JKj0XOKKrASht55qx38yabMTjDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Zone = _t, Date = _t, EVENTS = _t, Hall = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}, {"Zone", type text}, {"Date", type date}, {"EVENTS", type text}, {"Hall", type text}}),
    // Group by 'Zone' and 'Hall', and count 'Open' and 'Closed' events
    Grouped = Table.Group(#"Changed Type", {"Zone", "Hall", "EVENTS"}, {{"Count", each Table.RowCount(_), type number}}),
    // Pivot the table to have 'Open' and 'Closed' as columns
    Pivoted = Table.Pivot(Grouped, List.Distinct(Grouped[EVENTS]), "EVENTS", "Count", List.Sum),
    // Replace null values with 0
    ReplacedNulls = Table.ReplaceValue(Pivoted, null, 0, Replacer.ReplaceValue, {"Open", "Closed"}),
    // Filter rows with no 'Closed' event or more 'Open' events than 'Closed' events
    Filtered = Table.SelectRows(ReplacedNulls, each ([Closed] = 0) or ([Open] > [Closed])),
    // Extract unique zones from the filtered rows
    UniqueZones = List.Distinct(Table.Column(Filtered, "Zone")),
    // Create the final table
    Result = #table({"Area", "active zone", "all zones"}, {{"A-XF-1", Text.Combine(UniqueZones, ","), Text.Combine(List.Distinct(Table.Column(Source, "Zone")), ",")}})
in
    Result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Thanks a lot. 

The table above represents part of the data.

There are multiple Areas, A-XG-1, A-XT-2, etc. and each has different zones (H,K,L, etc). Can you make code general for all areas?

[ronrsnfld · Accepted solution]

The following code should handle various areas without any hard-coding.

Please read the code comments to better understand the algorithm:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="EventTbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}, {"Zone", type text}, {"Date", type date}, {"EVENTS", type text}, {"Hall", type text}}),
    
//Group by Area, Zone and Hall
// Then determine EVENT status for each most recent Date
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Area", "Zone", "Hall"}, {
        {"Status", (t)=>Table.Max(t, each [Date])[EVENTS]
        , type text}}),

//Group by Area
// then determine Open and All Zones
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Area"}, {
        {"Active Zones", (t)=>Text.Combine(List.Sort(List.Distinct(Table.SelectRows(t, each [Status]="Open")[Zone])),","), type text},
        {"All Zones", (t)=>Text.Combine(List.Sort(List.Distinct(t[Zone])),", "), type text}})
in 
    #"Grouped Rows1"

I combined your two tables to show results with two different areas. Also note that I changed the column headers for your first example to match what you showed in your second example. Depending on your actual data, you may need to make some column name changes in the code.

Source Data

Results

[Anonymous · Accepted solution]

ronrsnfld

Great Job! 😊

This code is perfect. Unfortunately, there is no "Accept as solution" button in your last post!

[ronrsnfld]

Not sure about that. When I view it, it already has an "accepted" box that is checked as accepted.