cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nicroco7
New Member

how to display 2 random cells from an excel Table ?

Hello,

 

I have a table with 2 columns. I need to display a random cell from the first column and a second cell from the other column.

I have no idea to do so.

I already do the random choice with a formula in excel so I only need to display it but I read that it's impossible. Maybe we can make the random choice inside powerApps.

 

Best regards,

Nicolas

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User III
Super User III

Hi @Nicroco7 

To refresh the random record, you would set a variable to random record by adding a button and setting the OnSelect property to the following:

Set(varRandom;
    First(Shuffle(Tableau1))
)

You would then set your label text to

varRandom.prenom

You can then click the button to refresh the random name.

View solution in original post

11 REPLIES 11
DylanSimons
Resolver III
Resolver III

you can use the Rand() function. This is chooses a number between 0 and 1, so if you have 100 rows you can do something like:

int(100*rand())

This is would give a random whole number between 0-100.

 

Then you can use First() and Lastn() to get that row.

 

Something like this:

First(Lastn(your excel connector ,int(100*rand()))).column

Nicroco7
New Member

Thank you for your answer but here is the error message. The translation is : there is an error in the formula, modify it and rerun it.

 

Do you think that I can write it in a text zone ? 

Yup! Just do another ".Value" on the end to get the text value. It might be something other than .Value, start typing "." And see what options show up.

timl
Super User III
Super User III

@Nicroco7 

An alternative to @DylanSimons's answer would be to call the Shuffle function. This function randomly reorders the records of a table.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-shuffle

The syntax would look like this:

First(Shuffle(Tableau1)).column1

 

@timl Your solution is great, I think I almost succeed but there is still a problem : it said "blank" while it isn't.

look at the pictures.

@DylanSimons Your solution doesn't work, it suggest nothing even with a ".". But the @timl method seems simple. Moreover, I haven't 100 cell in my columns. 

 

best regards,

Nicolas

@timl 's solution is much nicer than mine, glad it's working!

 

Are you putting that formula in the "default" property?

Nicroco7
New Member

I don't really know, I only click on "text label", then I replace ' "text" ' by @timl 's istruction. 

I think I write the formula on both. (Look at the pictures please)

timl
Super User III
Super User III

@Nicroco7 

This is strange because your formula looks correct.

To diagnose this further, if you omit the Shuffle function, can you confirm whether you can retrieve the first prénom value with the following function?

First(Tableau1).prénom

One other thing to try would be Filter the result of Shuffle to return non blank values.

First(Filter(Shuffle(Tableau1); prénom <> "").prénom

 

Well done ! The formula is correct because with your test I've the same problem. It's really weird. My table is ok (it's not "tableau1" but "TableauNom" but it doesn't work anyway). I will learn one more time how to create an excel table 😅

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,065)