Forum Discussion
Pull from Random Bin
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 )
)
Is there a way to Un-pivot in PBI? The original is too large to do so in Excel.
Of course there is. You do it in Power Query with just one click... Unpivot columns (Power Query) (microsoft.com)
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, )
If anyone is able to help, still struggling with getting multiple options here. Thanks a bunch!