Create bins for subcategories based on MIN and MAX values

[kirvis]

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:

1. Cell ID
2. Gene ID
3. Value

I want to create 10 bins for each Gene ID by doing (MAX(Value) - MIN(Value))/10 and then assign every row in my table the result.

Since my datasets are (very) large with millions of rows, I want to do this in Power Query and not in DAX. Once the bin is set, it will not change anymore.

Any ideas on how I can do this?

An example file with data can be found here.

Thanks!

[ImkeF]

Hi @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...

[kirvis]

Hi 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 

[ImkeF]

Hi @kirvis 

Sure that's possible - will send solution later.

[ImkeF · Accepted solution]

Hi @kirvis 

you have to turn the query into a function and apply it on the grouped data.

Please see file attached.

[kirvis]

Hi Imke,

Excellent, thanks! This is indeed exactly what I was looking for!

Best regards,

Kirvis