Forum Discussion
Mirror server updated sheet to another sheet
sconway1985 Your comments in italics, my comment in regular font below them:
In this workbook there is the raw data sheet which is updated automatically with data fed from SharePoint.
How is this done? Via PowerQuery?
I cannot make changes to that raw data sheet, such as formatting it as a table, because whenever new data is input into SharePoint, the sheet is reverted back to its original form as just raw data.
That's where I believe PowerQuery may come in. Set it up to connect to the data, do whatever cleaning and formatting you need and load it to the Data Model where you can do the DAX measures and from there you create the pivot tables.
But perhaps I'm totally missing the point. Difficult to visualize your project based on text only. At least for me.
Copying the sheet wouldn't be enough. This is an automated report that is updated each morning.
I need the mirror sheet to update as the raw data sheet is updated on it's own.
sconway1985 Perhaps you can connect to the raw date using Power Query and do whatever transformations are needed and load the result back into an Excel table. Every morning, refresh the query or set-up the query such that it automatically refreshes when the workbook opens and the "mirror" table will change with it.
- There isn't like a way to just mirror the sheet dynamically?
Similar to what a pivot table does whenever new data is entered?sconway1985 That's exactly where PQ comes in. Connect to a data source, load to Excel or the Data Model and then you can create a pivot table. Or you can pivot the data directly in PQ. Not sure what you want to do with the raw data.
- I'm trying to contain everything within the same workbook. This is an automated report so ideally I would have the sheet mirroring the raw data sheet, which I can not make permanent changes to as it is reverted when data is input via sharepoint.
So I need a separate sheet within the workbook referencing the data exactly. Currently right now I have the report built using pivot tables. That is a problem because there are some fields which are text input which doesn't in the pivot table unless I add "measures" which I cannot do unless the raw data sheet is in a table format.