Pull from Random Bin

[Thigs]

Hi! I have a rather complicated idea, so I'm simplifying it and hoping for some help. Essentially, I have a list of SKUs and a bin/location that each SKU is in. However, each SKU has three totally random locations. 

The locations are set at the beginning and can not repeat for the same SKU (ex - Apple's location 1 and 3 cannot both be Bin 1). 

Then I have some order data. For each order, a person selects the SKU randomly from it's locations (ex - they don't always pull from location 1). 

I can make the first table no problem - add three columns, use some rands and ifs to ensure they don't repeat, and we are good to go. It's the blue column in the second table I'm struggling with. How do I get Power BI to pull randomly from a row within the three columns? 

I have tried using Lookup Value, but that would be a set value. Maybe something like RandBetween(1,3), then lookup that column somehow?

Any help is greatly appreciated!!!

[daXtreme]

First and foremost, this format:

is not the right one to work with in PBI. PBI likes long-table format, not the one above.

Please unpivot this table (Unpivot columns (Power Query) (microsoft.com)) to:

SKU	Location ID	Location Name	Location Number
Apple	1	Location 1	1
Apple	2	Location 2	6
Apple	3	Location 3	8
Peach	1	Location 1	4
Peach	2	Location 2	9
Peach	3	Location 3	1

Then, if you want to create a base table with randomly pulled Location Numbers (this will change only on data refresh), you write this caclculated table in DAX:

// Orders should have a column with
// SKU so that we know which one
// to pull from the newly created
// table. We don't create any relationship
// between the tables.

[Your Table] = // calculated table
generate(
    allnoblankrow( Orders[OrderId], Orders[SKU] ),
    // Now, we'll be randomly pulling
    // a Location number from the new
    // table. Call it SKULocationLottery.
    var RandomLocationId = randbetween(1, 3)
    var CurrentSKU = Orders[SKU]
    var RandomLocationNumber =
        lookupvalue(
            SKULocationLottery[Location Number],
            SKULocationLottery[Location ID], RandomLocationId,
            SKULocationLottery[SKU], CurrentSKU,
            -1 // this should not happen!
        )
    return
        row( "Location Number", RandomLocationNumber )
)

[Thigs]

Is there a way to Un-pivot in PBI? The original is too large to do so in Excel. 

[daXtreme]

Of course there is. You do it in Power Query with just one click... Unpivot columns (Power Query) (microsoft.com)

[Thigs]

Awesome!

This is working perfectly, thank you so much!

Now I have one additional question if you don't mind - 

In an ideal world, I would have two additional columns. One for if there is only 1 location, another for 2 locations. I attempted to copy and paste the code sections to repeat this, however, it is not working. Can you see what is wrong with it?

[Your Table] = // calculated table

generate(

    allnoblankrow( Orders[OrderId], Orders[SKU] ),

    // Now, we'll be randomly pulling

    // a Location number from the new

    // table. Call it SKULocationLottery.

    var ThreeLocationId = randbetween(1, 3)

    var CurrentSKU = Orders[SKU]

    var ThreeLocationNumber =

        lookupvalue(

            SKULocationLottery[Location Number],

            SKULocationLottery[Location ID], ThreeLocationId,

            SKULocationLottery[SKU], CurrentSKU,

            -1 // this should not happen!

        )

     var TwoLocationId = randbetween(1, 2)

     var TwoLocationNumber =

        lookupvalue(

            SKULocationLottery[Location Number],

            SKULocationLottery[Location ID], TwoLocationId,

            SKULocationLottery[SKU], CurrentSKU,

            -1 // this should not happen!

        )

     var OneLocationId = 1

     var OneLocationNumber =

        lookupvalue(

            SKULocationLottery[Location Number],

            SKULocationLottery[Location ID], OneLocationId,

            SKULocationLottery[SKU], CurrentSKU,

            -1 // this should not happen!

        )

    return

        row( "Three Locations Number", ThreeLocationNumber, "Two Locations Number", TwoLocationNumber,  "On Locations Number", OneLocationNumber,    )

[Thigs]

If anyone is able to help, still struggling with getting multiple options here. Thanks a bunch!

[amitchandak]

@Thigs , You can subcategory index in the second column

That should help you to deal with this

https://www.youtube.com/watch?v=7CqXdSEN2k4

[Thigs]

So I can Subcategory Index, but that would give a set location for each as 1 - 3, correct? That does not have randomness?