A Question about Power Query

I am making a task management chart using Excel. The chart has a detail sheet and a summary sheet. The detail sheet has columns for major category tasks, minor category tasks, and progress. The detai...

excel

flexyourdata

Jul 29, 2023

shouronpou

You can use this query:

let
    Source = Excel.CurrentWorkbook(){[Name="Detail"]}[Content],

    // Group by Major Tasks, return a list column called Progress containing the Progress values for that major task
    Groups = Table.Group(Source, {"Major Tasks"}, {{"Progress", each _[Progress]}}),

    /* Add a column that counts the distinct Progress values for each major task
    If the count is greater than 1, return "In progress"
    Otherwise the count is 1, return the first item (they're all the same)*/
    Result = Table.AddColumn(Groups, "Status", 
                each 
                    if List.Count(
                            List.Distinct(_[Progress])
                        )>1 
                    then "In Progress" 
                    else _[Progress]{0}
            )[[Major Tasks], [Status]]
in
    Result

This will result in a summary table as shown in green:

Lorenzo

Silver Contributor

Jul 30, 2023

shouronpou

Similarly

let
    Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
    GroupedRows = Table.Group(Source, {"Major tasks"},
        {"Progress", (t)=> 
            if Table.RowCount(Table.Distinct(t, {"Progress"})) > 1
            then "In Progress"
            else Table.First(t)[Progress]
        }
    )
in
    GroupedRows

PQ_shouronpou.xlsx18 KB

shouronpou

Copper Contributor

Jul 30, 2023

Lorenzo
Thank you so much for your advice! It helped me a lot.

Lorenzo
Silver Contributor
Jul 31, 2023
shouronpou
You're welcome & Thanks for posting back

Forum Discussion

A Question about Power Query

Resources