A current customer request is to enable a linked Excel table driven by a Power BI dataset that can be filtered by one parameter for department and then filtered by individual staff within that department.
The request is then for each member of staff to make comments on their information which can then be stored as a snapshot in time to enable reporting on progress.
To enable this we have produced a Power BI report from some lovely customer specific dataflows, created a simple model, then created a table within Power BI.
We can use the fairly new feature of exporting data from a visual in the report as a linked table using summarized data. We are not using Analyze in Excel as this will give us a Pivot table which gives us a different granularity of data.
When you have created your linked Excel table, you can open the data.xlsx export and see your file. However, there are a few things you may wish to change – the order and the names of the columns. The spreadsheet currently brings in the name of the table as well as the name of the field which isn’t necessarily pretty.
My solution was to capture the query using performance analyzer within Power BI desktop, pop it into DAX Study and clean it up a bit. What do I mean by clean it up a bit, I mean remove the totals from the table at the start as that will remove the rollup element of the query, remove the topn element of the query and you should be left with a summarize columns query that gives you the core query.
I then used a SELECTCOLUMNS query to name each column specifically as the customer required thereby removing the name of the table – can’t get rid of the square brackets unfortnately and then used an order by to specify the order required.
Check that the query gives you the required result, then you can copy and paste into the Query window within Excel.
