topic Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query) in Desktop

How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

GreggPowell — Sat, 06 Aug 2022 07:33:50 GMT

How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query).

I have a table where the data is pulled from SharePoint. One column in the table I created from a measure

New Column = Table[Measure]

So, that column does not show up when I transform the data - so I can't use Power Query.

I want to use Dax to extract every Nth row (in my case exactly, the 1st and then every 7th row) into a new table.

Is that even possible?

Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

bcdobbs — Sat, 06 Aug 2022 08:15:44 GMT

Are you able to use power query to add an index column followed by a calculated column based on it using Number.Mod to your existing table?

You can then use Dax to create a calculated table along the lines of:

NewTable =
CALCULATETABLE (

OriginalTable,

ModColumn = 1

)

Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

GreggPowell — Sat, 06 Aug 2022 16:42:51 GMT

Had already added an index column. Problem is, I can't just tell it the row numbers I want to pick, because an API updates the data every day with a new row - so the whole thing must be automated.

I found this below - this is what I need to do.....

Problem is with this Excel formula:

=FILTER(data,MOD(SEQUENCE(ROWS(data)),3)=0)

I can't find the equivelent Excel ROWS() function in DAX - so I can replicate this.

I think the ROWS() function is the only incompatibility between DAX and Excel - not sure though.

Can you (or anyone) help me rewrite this in DAX?

THANKS!!!
Gregg P.
Arizona USA

Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

bcdobbs — Sat, 06 Aug 2022 17:25:01 GMT

What I was suggesting basically is that. Only difference is I'd suggest creating the modulo column in power query and then using the calculatetable to filter based on it.

Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

bcdobbs — Sat, 06 Aug 2022 17:31:33 GMT

I'm away from

a computer until Tuesday evening. Happy to build a demo at that point if no one gets there first.

Re: How to extract every Nth row (record) in an existing table into a new table (Not Using Power Query)

GreggPowell — Sat, 06 Aug 2022 18:17:25 GMT

You put me on the right track!

Thanks!

Here is what I did...

Extract_HR_PipeDrive_Deals = FILTER(ALL(HR_PipeDriveDeals_Running_Totals),HR_PipeDriveDeals_Running_Totals[Modulo]=6)