# 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
{"ft_lang_label":"__label__en","ft_lang_prob":0.7989976,"math_prob":0.7858745,"size":6260,"snap":"2019-35-2019-39","text_gpt3_token_len":1676,"char_repetition_ratio":0.1480179,"word_repetition_ratio":0.83183855,"special_character_ratio":0.2773163,"punctuation_ratio":0.20361248,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9697983,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-08-19T14:20:11Z\",\"WARC-Record-ID\":\"<urn:uuid:ba81abdb-7393-407e-bd3a-c864f5ce93c1>\",\"Content-Length\":\"80769\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:5a22cc12-218e-450f-a9c8-92ab5980c393>\",\"WARC-Concurrent-To\":\"<urn:uuid:edf853c7-adec-45c3-826b-11f85d9d6201>\",\"WARC-IP-Address\":\"104.108.114.97\",\"WARC-Target-URI\":\"https://social.technet.microsoft.com/Forums/en-US/d12b27d6-db9d-4f93-b725-b338b541a583/median-with-conditions?forum=powerquery\",\"WARC-Payload-Digest\":\"sha1:HKFVMXCHRI7WNEOFYHTJJ4Y7B3UXHPVC\",\"WARC-Block-Digest\":\"sha1:Q2BNTXPGCDJYWX7BE4R734PI5HIR72KP\",\"WARC-Identified-Payload-Type\":\"application/xhtml+xml\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-35/CC-MAIN-2019-35_segments_1566027314752.21_warc_CC-MAIN-20190819134354-20190819160354-00203.warc.gz\"}"}