cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lilmeh Frequent Visitor
Frequent Visitor

Create column with random values from list

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"}

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Create column with random values from list

@lilmeh 

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.

22.png


Best Regards,
Angelia

8 REPLIES 8
Super User
Super User

Re: Create column with random values from list

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

 


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

lilmeh Frequent Visitor
Frequent Visitor

Re: Create column with random values from list


@smoupre wrote:

Number.Random

 

or

 

Number.RandomBetween:


Hi, thanks for your reply but I was looking for solutions for text values.

tringuyenminh92 New Contributor
New Contributor

Re: Create column with random values from list

Same as we create sample data in excel, u just create one master table with 2 Column(id,text) and in fact table you add one column as reference column by random in range of id column of table master. After that make the relationship between 2 table.
Super User
Super User

Re: Create column with random values from list

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{#}


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

v-huizhn-msft Super Contributor
Super Contributor

Re: Create column with random values from list

@lilmeh 

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.

22.png


Best Regards,
Angelia

lilmeh Frequent Visitor
Frequent Visitor

Re: Create column with random values from list

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.

v-huizhn-msft Super Contributor
Super Contributor

Re: Create column with random values from list

Hi @lilmeh ,

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

tonmcg Regular Visitor
Regular Visitor

Re: Create column with random values from list

 

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

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 38 members 1,295 guests
Please welcome our newest community members: