cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asamadi Regular Visitor
Regular Visitor

me me e me

m

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Random number and random text in power bi query

I didn't save the pbix file.

 

Just follow the steps in this video.

 

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Ross73312 Super Contributor
Super Contributor

Re: Random number and random text in power bi query

Are you simply wanting to create a sample data set with some random values?  If so, its much easier to go about that in Excel and then import that data into Power BI


   

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

Proud to be a Datanaut!


   


Asamadi Regular Visitor
Regular Visitor

Re: Random number and random text in power bi query

i have no excel file, i have a table and i want to add these 2 columns in power bi ( Power Query)

Ross73312 Super Contributor
Super Contributor

Re: Random number and random text in power bi query

Really not sure why you would want to do that.  There is a random number generator in Power Query:

https://msdn.microsoft.com/en-us/library/mt253346.aspx

 

DAX also has a random number generator.

 

https://msdn.microsoft.com/en-us/library/ee634783.aspx


   

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

Proud to be a Datanaut!


   


Asamadi Regular Visitor
Regular Visitor

Re: Random number and random text in power bi query

Thanks for your response.

in Power Query it doesnt work, and it generates same numbers. and also I want to generate between 2 Numbers like: 1000 and 3000

Ross73312 Super Contributor
Super Contributor

Re: Random number and random text in power bi query

Random number generators give you a number between 0 and 1.  So if you wanted something thats between 1000 to 3000, you simply need to multiply the random number by 2000 and then add 1000 to the result.  Lastly just round to shave of the remaining decimals.


   

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

Proud to be a Datanaut!


   


MarcelBeug Super Contributor
Super Contributor

Re: Random number and random text in power bi query

You can add a dummy column with zeroes and add this to the lower limit of Number.RandomBetween, which forces different values on each row.

Unlike Excel's RANDBETWEEN,, Number.RandomBetween returns fractions, so you need to round and also correct the upper and lower with .5 to have the results evenly distributed.

I created a separate query Texts with the list of names.

 

let
    Source = #table({"Index"},List.Zip({{1..100}})),
    AddedZero = Table.AddColumn(Source, "Zero", each 0),
    AddedRandomNumber = Table.AddColumn(AddedZero, "RandomNumber", each Number.Round(Number.RandomBetween([Zero]+999.5,200000.5),0,RoundingMode.TowardZero)),
    AddedRandomText = Table.AddColumn(AddedRandomNumber, "RandomText", each Texts{Number.Round(Number.RandomBetween([Zero]-.5,-.5+List.Count(Texts)),0,RoundingMode.TowardZero)}),
    RemovedZero = Table.RemoveColumns(AddedRandomText,{"Zero"})
in
    RemovedZero

 

Specializing in Power Query Formula Language (M)
Asamadi Regular Visitor
Regular Visitor

Re: Random number and random text in power bi query

Thanks for your response

is it possible to send me pbix file?

i couldnt run the code you create.

MarcelBeug Super Contributor
Super Contributor

Re: Random number and random text in power bi query

I didn't save the pbix file.

 

Just follow the steps in this video.

 

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Asamadi Regular Visitor
Regular Visitor

Re: Random number and random text in power bi query

Thanks Thanks 

Thanks a milion

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 63 members 1,373 guests
Please welcome our newest community members: