Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I want to define a list a text values, and then create a new column which selects any value from that list.
Can anyone please tell me how to select any value from a list?
e.g. create a new column containing any values from {"Apples", "Bananas", "Pears"}
Solved! Go to Solution.
@Anonymous
Please create a Rank column which display the location of text cell.
Rank = RANKX(Table1,Table1[Name],,ASC)
Then, we select any location according the random number between 1 to the max location. Use LOOKUPVALUE function grab the values.
New Column = LOOKUPVALUE(Table1[Name],Table1[Rank],RANDBETWEEN(1,MAX(Table1[Rank])))
Please refer to the following screenshot.
Best Regards,
Angelia
Given your list of words, here's a method that creates another list that contains 1 million items randomly selected from the initial list:
let list = {"Apples", "Bananas", "Pears"}, shuffle = List.Generate( () => 1, each _ <= 1000000, each _ + 1, each list{Number.RoundDown( Number.RandomBetween(0, List.Count(list)) )} ) in shuffle
@Anonymous
Please create a Rank column which display the location of text cell.
Rank = RANKX(Table1,Table1[Name],,ASC)
Then, we select any location according the random number between 1 to the max location. Use LOOKUPVALUE function grab the values.
New Column = LOOKUPVALUE(Table1[Name],Table1[Rank],RANDBETWEEN(1,MAX(Table1[Rank])))
Please refer to the following screenshot.
Best Regards,
Angelia
Thanks Angelia - I couldn't get randbetween and lookupvalue to work together in the latest Power BI Desktop version but separating the two into separate columns seems to be a workaround for my issue. Thanks.
Hi @Anonymous ,
I am very happy for you have resolved your issue. You'd better mark the corresponding reply which will help others find the solution easily. Thank you very much.
Best Regards,
Angelia
Sounds like you are talking "M" Power Query language. If that is the case, you could use:
Number.Random
https://msdn.microsoft.com/en-us/library/mt253346.aspx
or
Number.RandomBetween:
https://msdn.microsoft.com/en-us/library/mt253327.aspx
If you want to do it in DAX, the only thing I can think of would be Sample:
https://msdn.microsoft.com/en-us/library/mt163692.aspx
@smoupre wrote:Number.Random
or
Number.RandomBetween:
Hi, thanks for your reply but I was looking for solutions for text values.
OK, I guess I thought you'd get where I was going with that. Use the random number to retrieve that item from the list. For example, let's say that your list is has five items in it, and you call it "MyList", you could do something like:
MyList{Number.RandomBetween(0,4)}
List positions start at 0, you can reference a specific list position by using the syntax: Listname{#}
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |