Hello,
I am using power query to present a table on excel from a CSV source file that gets updated each day by someone saving over it every morning.
It's a simple CSV file, the headers are separated by | but they have inconsistent space between them so, the next day excel can't match the header on the table to the CSV file header.
Is there any way to get excel to ignore the space?
D
Can you attach a sample csv file?
Go Advanced -> Manage Attachments -> Upload
CSV file attached
Re: Excel Power Query- The headers from the CSV source table can change.
I assume the csv is exact the same every day (except data of course)
so maybe try this way:
- load csv to the PQ
- don't change anything
- create reference
- transform reference as you wish
I can't check more because of one csv. To compare minimum is two
but what are you tryin' to achieve?
Last edited by sandy666; 06-29-2018 at 04:36 AM.
The CSV file is coming from a different software, and it has a strange way of changing the formatting of the data, including the headers.
capture1.PNG
As you can see it the headers have spaces in them, this would be fine because I can transform reference but the problem is that next day there might be an additional space and excel can match the headers.
I want the excel to allow the user to refresh the table daily and get the updated CSV data.
so next day = new csv with the same name of file, the same name of headers but contain different number of spaces?
maybe:
- Use headers as first row
- trim
- Use first row as headers
describe whole procedure from start to the end if it possible
you cannot transform any data before load to PQ.
Maybe try any VBA solution which trim spaces of headers then load to PQ
Last edited by sandy666; 06-29-2018 at 05:39 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks