power query - condtitional index column by category and subcategory

[parry2k]

@ImkeF

Need clustered index column by job and product based on a condition on a value column. See attached worksheet with sample data.

https://drive.google.com/file/d/1JRSV9niwBpFbCtLUeyyoDK0obl8qLRvg/view?usp=sharing

Thanks in advance,

P

[ImkeF · Accepted solution]

Just split your source-table into 2: First >=2 and add the clustered index like I've described and add a column with -1 to the others before combining again:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job", Int64.Type}, {"Product", Int64.Type}, {"Date", type datetime}, {"Value", type number}, {"Desired Result", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Value] >= 2),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Job", "Product"}, {{"Index", each Table.AddIndexColumn(_,"Index", 1,1), type table}}),
    ClusteredIndex = Table.Combine(#"Grouped Rows"[Index]),
    #"Filtered Rows1" = Table.SelectRows(Source, each [Value] < 2),
    Other = Table.AddColumn(#"Filtered Rows1", "Index", each -1),
    #"Appended Query" = Table.Combine({Other, ClusteredIndex})
in
    #"Appended Query"

[parry2k]

Thanks it worked. I have to test it on approx. 2 million rows table and I hope performance will be ok.