filter nested table rows

[Kraius]

This is hopefully an easy one.  I'm working with Power Query (or M, i guess) in the Desktop software and am importing a bunch of spreadsheets (with same schema) into some tables.  One of the tabs in the sheet has some useful info in the first 30 rows and then just a ton of junk for the next few thousand rows that i'll never need (with a bunch of #N/A data too, which breaks my excel data connector).  I don't own this data so it's kinda a manual effort to drop the data out of the tab pre-import into BI. 

I know M/Power Query has the functions for FirstN rows and the like, but i can't figure out if there is a way to apply it to a table nested within a row in the data (before you expand that nested table into new columns).  Ideally, I'm hoping there is a way to tell Power BI to "take this sheet/tab from this xlsx, crop it down to just X rows, expand it into working rows/columns and do fancy transforms".  Can you somehow nest a FirstN function into an ExpandColumns function?

[v-caliao-msft]

@Kraius,

 Please refer to the link below to see how to get dynamic Top N rows in Power Query.

1. Source = Csv.Document(File.Contents("NameRemoved.csv"),null,",",null,1252)

2. #"FilteredRows" = Table.SelectRows(Source, each Text.Contains([Column1], "Daily Usage")),

3. #"Position of Daily Usage" = Table.PositionOf(Source, FilteredRows {0}),

4. #"TopRemoved" = Table.Skip(Source, (#"Position of Daily Usage" + 1)),

5. #"First Row as Header" = Table.PromoteHeaders(#"TopRemoved"),

http://blogs.adatis.co.uk/danevans/post/Dynamic-Top-N-rows-in-Power-Query

Regards,

Charlie Liao