Newbie to power pivot/query - question about linkback tables?

[Guitarmageddon]

Hello!
So I have recently discovered the power of the power query/excel data model features. My initial exposure was in my efforts to compile a bunch of like files into one long list, so that other co workers could filter through them for data as they desired.

So that worked great- i figured out how to power query/compile, and output to one large combined table. Catch is, if I do it for ALL of the files I desire (back to the beginning of the year in our '2018' folder which gets updated weekly) it exceeds excels row limit. I have right now 2.1 million lines of data.

So I learned I could load all 2.1 million lines directly to the data model, so I did that and it worked great.

Now my problem is, how do I: 1)most easily pull that data BACK out of the data model in essentially the same flat table format (no fancy pivots needed on this one) and 2)since I will hit the row limit, how do I make this output table just dynamically pull the last 90 days or whatever i stipulate? That can be based off an "entry date" column within the data.

I have done a preliminary search and read about DAX queries and that I might want to use those, because what I want is a "reverse linkback" table....i think. But now we're getting into me reading Egyptian.

I have so far loaded all my millions of lines to the data model, cleaned it all up in power query, and then went to "existing connections" then selected my query, and loaded as "table." But this is where it gets stuck and says "query returned more data than will fit on a worksheet" and i continue with as many as I can.

Can anyone lend me a hand and see if this is what I really want? Thanks a lot!

 

[peter789]

Two ways I can see.
Firstly
Ignore the data model. Filter your data in the query to just show the last XX days then load the result to an Excel table.
Or Secondly
Add a Calendar to your data model and link to your date field.
Insert a pivot Table based on the data model and use the date field in a Timeline slicer to select the date range you require.
P

 

[Guitarmageddon]

Two ways I can see.
Firstly
Ignore the data model. Filter your data in the query to just show the last XX days then load the result to an Excel table.
Or Secondly
Add a Calendar to your data model and link to your date field.
Insert a pivot Table based on the data model and use the date field in a Timeline slicer to select the date range you require.
P

So just to clarify, I can load right to the query, filter there, then the resulting table that I choose to send to an excel sheet will automatically be filtered on my set date range? I noticed I can set "last XX days" or a filter like that. What Im having trouble with is getting the resulting table to update whenever new files are dropped into the source folder, think Im doing something wrong.

So if I bypass the data model, Id be going to load data from file , from folder. Select my folder. Then combine and edit. From there, make all my changes, and apply my date filter. Then once I close and load, I can send it straight to a worksheet table. Now in the future when new files are dropped into the parent directory, the query will pick them up when I hit refresh all? Or am I missing a step

 

[peter789]

That's pretty much it. You need to Refresh to get the latest files.
Keep working with Power Query and with a little help from the internet you'll soon pick up tricks to make it a bit more automatic; for example passing date filter parameters from your sheet back into the query so you don't have to manually alter the filter. You might want to add a Macro button to run the refresh as well.
Don't give up with Power Pivot though. It's a bit harder to learn DAX than PQ but the results are really worth it.
P

 

[Guitarmageddon]

That's pretty much it. You need to Refresh to get the latest files.
Keep working with Power Query and with a little help from the internet you'll soon pick up tricks to make it a bit more automatic; for example passing date filter parameters from your sheet back into the query so you don't have to manually alter the filter. You might want to add a Macro button to run the refresh as well.
Don't give up with Power Pivot though. It's a bit harder to learn DAX than PQ but the results are really worth it.
P

Thanks a lot for the input. Any great "launch pad" source you used to really get your knowledge on this going? Im wondering if I can find a good course via Lynda.com that I get free via my university.