Search code examples
ssaspowerbidaxpowerquery

Replace a percentage of values in a column with another

I have a tricky problem, maybe one of you can help.

I have a column with several distinct values, one of which is "Not Available". I would like to create a calculated column by replacing those "Not Available" values with 4 new values, say "Value 1", "Value 2", etc.. distributed equally i.e. 25% will be "Value 1", 25% will be "Value 2" etc. I really appreciate any help I can get. I should mention that my data lives in SSAS TDM so I can't use the query editor.

Thanks!

Below you will find some test data along with my desired results. 

Example = 
DATATABLE (
    "Value", STRING,
    {
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Not Available" },
        { "Bike" },
        { "Bike" },
        { "Bike" },
        { "Bike" },
        { "Skateboard" },
        { "Skateboard" },
        { "Skateboard" },
        { "Skateboard" },
        { "Skateboard" },
        { "Skateboard" },
        { "Snowboard" },
        { "Snowboard" },
        { "Snowboard" },
        { "Snowboard" },
        { "Snowboard" },
        { "Snowboard" },
        { "Penny Board" },
        { "Penny Board" },
        { "Penny Board" },
        { "BMX" },
        { "BMX" },
        { "BMX" }
    }
)

Desired results:

enter image description here

Solution

  • As in the other answer, I will assume you have an indexed replacement table. I will also assume you can create a unique index for your Example table (that's a separate question).

    The logic is the same, but now as a DAX calculated column:

    Value2 =
IF (
    Example[Value] = "Not Available",
    LOOKUPVALUE (
        'Replace'[Value],
        'Replace'[Index], MOD ( Example[Index], COUNTROWS ( 'Replace' ) )
    ),
    Example[Value]
)