Analyze in Excel Filter

[mroberts_troy]

So, I've been using the Analyze in Excel trick to drill through to the data in Excel for a long time, however it continues to be the source of so much pain, so I'm reaching out to the community to hopefully find help.

My approach is to use "Analyze in Excel" to generate the connection from Power BI into Excel.  In Excel I want to only recieve data that matches a certain cell value elsehwere in the workbook.  So, for example, if I'm pulling all the rows of timesheet data from Power BI, I want to only pull in the rows that match the date listed in a named cell elsewhere in the workbook.  However, I am so unfamiliar with what language the OLE DB Query uses, I really keep running into the limit of my Google search abilities.

Can someone help me understand, what should this query should be, in order to allow me to filter the incoming data by a particular (named) cell value?

[collinq]

Hi @mroberts_troy ,

My first thought is, why not just make the Power BI query do that for you?  If you are using a Timesheets query in Power BI, you can have a filter in the Power BI report that is the field that you are looking for.  And then, you can do the Export Data and select "Summarized Data" and you will get exactly what you are wanting.

Secondly, if you are bringing data into Excel from an existing query the best bet then (in my not-Excel-expert opinion) is to use the Vlookup which can do this (VLOOKUP function (microsoft.com)) .  Or, inside of Excel you can do a pivot table using the fields that you want.

However, since you have a start in Power BI, why not let the tool do the heavy lift for you in the first place?

[mroberts_troy]

---

@collinq wrote:

Hi @mroberts_troy ,

My first thought is, why not just make the Power BI query do that for you?  If you are using a Timesheets query in Power BI, you can have a filter in the Power BI report that is the field that you are looking for.  And then, you can do the Export Data and select "Summarized Data" and you will get exactly what you are wanting.

Secondly, if you are bringing data into Excel from an existing query the best bet then (in my not-Excel-expert opinion) is to use the Vlookup which can do this (VLOOKUP function (microsoft.com)) .  Or, inside of Excel you can do a pivot table using the fields that you want.

However, since you have a start in Power BI, why not let the tool do the heavy lift for you in the first place?

---

Because there will be one Excel file per week.  Power BI will hold all weeks.  So when absorbing the data from Power BI, the Excel file needs to limit itself to only get data from the week for that particular file.  If I don't enforce this kind of query filter, then as time goes by, the Excel files will continue to grow in size, dramatically.

So, by having a named cell that contains the week-ending date, and having the query restrict the incoming data, each Excel file will only contain the data from Power BI that matches that date.  Make sense?

[collinq]

Hey @mroberts_troy ,

I understand the need for having only the new data.  You could have incremental refresh setup in Power BI and have the date filter in there to only export the latest data.

If you are trying to avoid the Power BI bits then I would suggest that you use Excel to connect to Power BI and then to use Power Query in Excel and put the filter by date in there.  These articles can help you through the process:  Excel | Microsoft Power Query     About Power Query in Excel (microsoft.com)

[mroberts_troy]

The problem is that Excel can't Power Query directly into Power BI.  The only way to get the data into Excel is using the "Analyze with Excel" and then drillthrough, which is what I'm doing now.  And an incremental refresh in Power BI assumes that people will always be working on the payroll timesheet that is current or latest, but that's not the case.  It may happen that we need a refresh on a file that's a week or two stale, so the incremental data would be gone by then.