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.
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.
- Power Query worked a treat once I figured out how to use it and determined where the master excel template lived.
Thanks for pointing me in the right direction. - I'm not 100% sure how the data is updated. I know it lives on a reporting server and the guy who set it up was primarily working in Python. The company has our own portal.cloud site where I believe the build lives.
I have 0 experience in PowerQuery.
There isn't just a simple way within the workbook itself to say "This sheet = This sheet", similar to say a vlookup?