Forum Discussion

shouronpou's avatar
shouronpou
Copper Contributor
Jul 29, 2023
Solved

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 detail sheet has a column for major category tasks and a column for progress, and I want to enter the column for progress according to the following conditions.

  • When the progress column of all subcategory tasks belonging to a task in a major category is "completed" on the detail sheet, "completed" is displayed in the progress column on the summary sheet.
  • When the progress column of all subcategory tasks belonging to a task in a major category is "new" on the detail sheet, "new" is displayed in the progress column on the summary sheet.
  • Otherwise, "in progress" is shown in the progress column of the summary sheet.

I am trying to run this process through a power query. When I load the contents of the detail sheet into a power query and run the grouping on the task column of the major category, I see "Table" on each new column row. How can I output the progress columns from here to meet the expected conditions?

excel
  • flexyourdata's avatar
    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:

     

     

5 Replies

  • Lorenzo's avatar
    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
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    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:

     

     

Resources