Log In
Register
Home
Power Pivot
September 2, 2020
Offline
Hi,
I like your examples with PowerQuery combining multiple csv-files and creating great pivots and graphs.
We started to rebuild our PerformancePortal with these techniques.
What is the best way to implement a change in file content? For instance: at some moment we want/need to have extra columns to show specific information. Is it possible to include newer columns into the 'merged' data model without having to change all previous csv-files?
kind regards, Maarten
See Answer
November 8, 2013
Offline
Hi Maarten,
See this post for a solution, download that file attached: https://www.myonlinetraininghu.....der#p16593
Power Pivot
September 2, 2020
Offline
Thanks for the response.
But: apparently it will not or cannot work for files from a SharePoint map. Do you have a likewise solution for that environment?
And I suspect that this solution deals with the adding of new files, but my real question was about file content.
For instance we have a lot of monthly files with project data, and starting from this month we want to register the responsabel account manager,
that will be registered in a new column.
November 8, 2013
Offline
What is a SharePoint Map? Is that a sharepoint site documents?
If yes, you can simply Sync that folder locally, and you will always have files syncronized with sharepoint site. Use the local synced folder as source for extracting data.
Power Pivot
September 2, 2020
Offline
The power of using files from a SharePoint folder is that everyonbe with SharePoint rights can update the file; that's not possible when we use a synced folder.
But still no answer to my question: how to deal with new (necessary) columns.
November 8, 2013
Offline
Actually, the link I provided offers a solution where it does not matter how many columns are in the source files, so you do have an answer. My question in fact does not have an answer 🙂 ("What is a SharePoint Map?")
All you have to do is to change the query to get data from sharepoint folder instead of local folder. The function that reads the files will consider any new columns added.
November 8, 2013
Offline
The power of using files from a SharePoint folder is that everyonbe with SharePoint rights can update the file; that's not possible when we use a synced folder.
It's about how we read the data, not how we EDIT files.
Syncing the sharepoint folder with your desktop will only change how you READ the files: you read them from the local folder, instead of reading from web.
OneDrive Sync agent will take care of bringing the online changes to your computer, so I did not suggested editing the source files OFFLINE.
Power Pivot
September 2, 2020
Offline
I'm sorry for the misunderstandings
- in Dutch a 'map' stands for a 'folder', so I mean the csv-files are in a SharePoint folder
- with "everyone with SharePoint rights can update the file" I mean 'update the data' within the Excel file with the Power Pivot and graphs (also located in SharePoint) , not the underlying cvs-files
- "The function that reads the files will consider any new columns added"; I just tried that, but the extra columns in the newest csv-files didn't show up, so what function do you mean?
November 8, 2013
Offline
Please upload a few sample csv files with different headers and just 1-2 rows of fake data.
Will prepare a sample.
Power Pivot
September 2, 2020
Offline
I have some data csv files and a simple Excel sheet to read them.
The first 3 (folder Data) are the 'standard' ones, in the folder NewData I added one with extra columns.
In the Excel sheet I got the data of the first 3 by 'reading csv from folder'.
After making pivot table I placed the 4th file and updates all data, but no extra columns.
October 5, 2010
Offline
Hi Maarten,
Did you mean to attach files? You need to click the Start Upload button after selecting the files.
Phil
Power Pivot
September 2, 2020
Offline
Sorry, I didn't notice that!
November 8, 2013
Offline
Using the automatic file combiner is not dynamic. It ONLY reads the first file in that folder to get the headers and ignore all other headers from ALL other files.
Just use the file I already provided.
File attached here in case you don't want to download it from the other post i indicated.
Note that I had to change the csv delimiter, in your csv's the delimiter is semicolon instead of comma.
1 Guest(s)
