none
Median with conditions

    Question

  • Hello all

    first of all, sorry my english is not that good, but I haven't found a solution on german websites.

    How can I calculate median values with conditions in Power Query?

    I have a list with time periods for each project phase and this projects have different business cases.

    e.g. the list looks like

    Projectname    Case        Period Phase 1           Period Phase 2

    ProjectA           B2B          20d                            30d
    Project B          FttX          10d                            20d
    Project C          B2B          15d                            19d

    and so on. The original list has some thousand rows.

    Now I wanna add a column with the median for each perdion but with the condition "Case".
    So, median for Perio Phase 1 and B2B would be 17.5ddand for FttX 10d.

    How is this possible?
    I hope you can understand my problem, again, sorry for my English ;-)

    BR
    Marco

    Tuesday, May 21, 2019 8:05 PM

Answers

  • You have to use "Group by" function. Right-click your "Case" column and click on "Group By". Then under the "New column name" write a column name, select "Median" as operation and "Period Phase 1" as Column.

    However, "Period Phase 1" isn't a number but text. To get median, you have to transform it into pure numbers or make a new column with only the number of "Period Phase 1".

    In code, you would have something like this. Replace step and column names with what you have.

    #"Grouped Rows" = Table.Group(#"YOUR STEP NAME HERE", {"Case"}, {{"Median", each Text.From(List.Median(NumberFrom(Text.BeforeDelimiter([Period Phase 1], "d")) ))&"d" , type text}})

    In the above code, I extracted the numbers from Period Phase 1 already.

    If you want to keep the other columns, it's better if you join this new table back onto the old table. If you paste your Power Query code for this table, we could try to help with that



    Wednesday, May 22, 2019 12:41 PM
  • A complete solution that would add new median columns (Median Phase 1, Median Phase 2) to the original table, and maintain the original sort order would look like the following:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        addedSortIndex = Table.AddIndexColumn(source, "Index", 0, 1),
        replacedValue = Table.ReplaceValue(addedSortIndex,"d","",Replacer.ReplaceText,{"Period Phase 1", "Period Phase 2"}),
        changedType = Table.TransformColumnTypes(replacedValue,{{"Period Phase 1", Int64.Type}, {"Period Phase 2", Int64.Type}}),
        groupedRows = Table.Group(
            changedType, 
            {"Case"}, 
            {"Table", (i) => Table.AddColumn(i, "Median Phase 1", each Number.ToText(List.Median(i[Period Phase 1])) & "d"), type table}
        ),
        addedColumn = Table.TransformColumns(
            groupedRows, 
            {"Table", (i) => Table.AddColumn(i, "Median Phase 2", each Number.ToText(List.Median(i[Period Phase 2])) & "d")}
        ),
        combinedTables = Table.Combine(addedColumn[Table]),
        transformedRows = Table.FromRecords(
            Table.TransformRows(
                combinedTables, 
                (i) => Record.TransformFields(i, {{"Period Phase 1", each Number.ToText(_) & "d"}, {"Period Phase 2", each Number.ToText(_) & "d"}})
            )
        ),
        sortedRows = Table.Sort(transformedRows,{{"Index", Order.Ascending}}),
        removedSortedRows = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedSortedRows

    If you don't need to maintain the original sort order, you can remove the addedSortIndex, sortedRows, and removedSortedRows steps.

    Wednesday, May 22, 2019 10:06 PM

All replies

  • You have to use "Group by" function. Right-click your "Case" column and click on "Group By". Then under the "New column name" write a column name, select "Median" as operation and "Period Phase 1" as Column.

    However, "Period Phase 1" isn't a number but text. To get median, you have to transform it into pure numbers or make a new column with only the number of "Period Phase 1".

    In code, you would have something like this. Replace step and column names with what you have.

    #"Grouped Rows" = Table.Group(#"YOUR STEP NAME HERE", {"Case"}, {{"Median", each Text.From(List.Median(NumberFrom(Text.BeforeDelimiter([Period Phase 1], "d")) ))&"d" , type text}})

    In the above code, I extracted the numbers from Period Phase 1 already.

    If you want to keep the other columns, it's better if you join this new table back onto the old table. If you paste your Power Query code for this table, we could try to help with that



    Wednesday, May 22, 2019 12:41 PM
  • A complete solution that would add new median columns (Median Phase 1, Median Phase 2) to the original table, and maintain the original sort order would look like the following:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        addedSortIndex = Table.AddIndexColumn(source, "Index", 0, 1),
        replacedValue = Table.ReplaceValue(addedSortIndex,"d","",Replacer.ReplaceText,{"Period Phase 1", "Period Phase 2"}),
        changedType = Table.TransformColumnTypes(replacedValue,{{"Period Phase 1", Int64.Type}, {"Period Phase 2", Int64.Type}}),
        groupedRows = Table.Group(
            changedType, 
            {"Case"}, 
            {"Table", (i) => Table.AddColumn(i, "Median Phase 1", each Number.ToText(List.Median(i[Period Phase 1])) & "d"), type table}
        ),
        addedColumn = Table.TransformColumns(
            groupedRows, 
            {"Table", (i) => Table.AddColumn(i, "Median Phase 2", each Number.ToText(List.Median(i[Period Phase 2])) & "d")}
        ),
        combinedTables = Table.Combine(addedColumn[Table]),
        transformedRows = Table.FromRecords(
            Table.TransformRows(
                combinedTables, 
                (i) => Record.TransformFields(i, {{"Period Phase 1", each Number.ToText(_) & "d"}, {"Period Phase 2", each Number.ToText(_) & "d"}})
            )
        ),
        sortedRows = Table.Sort(transformedRows,{{"Index", Order.Ascending}}),
        removedSortedRows = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedSortedRows

    If you don't need to maintain the original sort order, you can remove the addedSortIndex, sortedRows, and removedSortedRows steps.

    Wednesday, May 22, 2019 10:06 PM