Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Group & generate a list

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.

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    ronrsnfld

    Great Job! ğŸ˜Š

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

7 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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