Forum Discussion
Advocate IIAnalyze in Excel Filter
Super UserHi 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.