Forum Discussion

Pbiuserr's avatar
Pbiuserr
Icon for Post Prodigy rankPost Prodigy
3 years ago

TOP5 for few columns from table

Hello, 
I want to retrive 5 random rows from few columns of certian table, ideally to let them present different value

so far I use

EVALUATE

TOPN(5, Table) 
lets assume I want Column1, 2, 3 from this table? When I do TOPN(5, SUMMARIZECOLUMNS(Table[Column1], Table[Column2]...) then I get the values grouped by, so basically 5 same values

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could create a table, or a table variable, like

    Random Rows =
VAR NumRows =
    COUNTROWS ( 'Table' )
RETURN
    GENERATE (
        GENERATESERIES ( 1, 5, 1 ),
        VAR RandIndex =
            RANDBETWEEN ( 1, NumRows )
        RETURN
            SELECTCOLUMNS (
                INDEX ( RandIndex, 'Table', ORDERBY ( 'Table'[index], ASC ) ),
                "col1", 'Table'[col1]
            )
    )

    You would need an index column, preferably numeric, to uniquely identify a row. If you don't have one in the data you can add one using Power Query