Forum Discussion
kirvis
Helper I
Helper ICreate bins for subcategories based on MIN and MAX values
Hi all, I am working on a project for which I would like to create bins based on the MAX and MIN of a value within a subset. The dataset has 3 columns: Cell ID Gene ID Value I want t...
ImkeF
Community Champion
Community ChampionHi kirvis
please check this query:
let
Source = Excel.CurrentWorkbook(){[Name="Expression_FACT"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GeneID", Int64.Type}, {"Cell ID", Int64.Type}, {"Value", type number}}),
ListOfValues = List.Buffer(#"Changed Type"[Value]),
Min = List.Min(ListOfValues),
Max = List.Max(ListOfValues),
Increment = (Max - Min) / 10,
Buckets = List.Transform({1..10}, each [Index = _, Value = Min + (_ * Increment) ] ),
#"Converted to Table" = Table.FromList(Buckets, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Index", "Value"}, {"Index", "Value"}),
Custom1 = #"Expanded Column1" & #"Changed Type",
#"Sorted Rows" = Table.Buffer(Table.Sort(Custom1,{{"Value", Order.Descending}, {"Index", Order.Descending}})),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([GeneID] <> null))
in
#"Filtered Rows"
This is not the fastest operation one can run...
kirvisHelper I
Helper IHi Imke,
Thanks for your response. The query seems to work like a charm (and is quite fast as well).
However, I realize that I might not have made myself completely clear in my original post: I would like to do exactly this, but then for each GeneID separately. In your query, you determine the MIN and MAX of all values combined, so of all GeneIDs together.
Is there an easy way to do exactly what you have done, but then grouped per GeneID?
Thanks,
Kirvis