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.
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.
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.
sconway1985 Sorry, don't understand. It's all a bit abstract.
- I have an automated report contained within one workbook.
In this workbook there is the raw data sheet which is updated automatically with data fed from SharePoint.
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.
The bulk of my report is made with Pivot tables referencing that raw data sheet. These pivot tables update whenever the data sheet updates.
I have text inputs in my SharePoint list that I would also like to feed my report. As it stands with the pivot tables I can not do this.
To do this with a pivot table, I would need to format the raw data sheet as a table. This would allow me to add "Measures" to the pivot table's fields via DAX.
I need someway to mirror the raw data sheet exactly and dynamically within the same workbook.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.