Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-huizhn-msft
Employee
Employee

@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.

22.png


Best Regards,
Angelia

View solution in original post

8 REPLIES 8
tonmcg
Resolver II
Resolver II

 

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

 

v-huizhn-msft
Employee
Employee

@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.

22.png


Best Regards,
Angelia

Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable


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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.