generate a one-column table that contains hundreds of different categories using M or DAX

Question

I need to split my products into a total of 120 predefined price clusters/buckets. These clusters can overlap and look somewhat like that:

As I dont want to write down all of these strings manually: Is there a convenient way to do this in M or DAX directly using a bit of code?

Thanks in advance! Dave

Answer 1

With m-Query you can create a function. Open the query editor. Richt click and create empty query. Create function (ignore warning) and call it : RowGenerator.

Open advanced editor and past the following code:

let
    Bron = (base as number, start as number, end as number) => let
       Bron =  Table.FromList(List.Generate(() => start, each _ <= end, each _ + 1), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
       #"Aangepaste kolom toegevoegd" = Table.AddColumn(Bron, "Aangepast", each Number.ToText(base) & " - " & Number.ToText([Column1]))
    in 
        #"Aangepaste kolom toegevoegd"
in
    Bron

This function creates a table where base is your first number and start, end the range.

Add another empty query, open the advanged editor and paste:

let
    Bron =  List.Generate(() => 0, each _ < 5, each _ + 1),
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Aangeroepen aangepaste functie" = Table.AddColumn(#"Geconverteerd naar tabel", "test", each RowGenerator(_[Column1], _[Column1] + 1, 5)),
    #"test uitgevouwen" = Table.ExpandTableColumn(#"Aangeroepen aangepaste functie", "test", {"Column1", "Aangepast"}, {"Column1.1", "Price Cluster"}),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"test uitgevouwen",{"Column1", "Column1.1"})
in
    #"Kolommen verwijderd"

This creates first a list of 5 rows, then it calls the previous made function for each row and the last step is to expend the rows and remove the not needed columns.

Enjoy: