none
Power Query Refresh RRS feed

  • Question

  • I receive daily .csv files via e-mail (unfortunately I can't access the original source). Each data file contains information for yesterday, today, and tomorrow. Therefore, the most accurate information is recorded for the earliest date for each data file.

    I am trying to create a query or multiple queries to pull data from multiple .csv files and be able to do the following:

    1. Pull the earliest date from EACH file and de-dup some rows based on criteria. I tried doing this, but it seems to pull the earliest date not by file, but from all the .csv files. So, if I wanted the 2 Feb from one file and 3 Feb from another, it's only pulling the "earliest" from both (2 Feb).

    2. Refresh ONLY new .csv files I add to the folder. I need to manually go through some of the rows and delete due to issues from the data source, so when I refresh I don't want it to run through the other files. 

    Thoughts? I was thinking maybe there's a way to create a table and append it with the new data somehow, but I get stuck in the process.

    Thanks!

    Friday, March 2, 2018 7:41 PM

Answers

  • Your situation is hard to understand without seeing the tables and your source data.   

    For1

    You should just use Get and Transform in Excel 2016.   Why you first clean up data manually makes no sense.   To stop the history data from refreshing you should load each new data set as a separate table.

    Try using a standard title for each column Header eg. date1 date2 etc with your actual date titles such as 2 February in row1 and actual data starting in row2.   Get and Transform in Excel 2016 has the ability to Promote first row.   So every time you load new data your Yesterday column will always be first no matter what the actual date.

    For2

    Refresh all tables will work because your history tables aren't changed at the source.

    Hope this helps.   Good luck

    Sunday, March 4, 2018 7:30 PM

All replies

  • Your situation is hard to understand without seeing the tables and your source data.   

    For1

    You should just use Get and Transform in Excel 2016.   Why you first clean up data manually makes no sense.   To stop the history data from refreshing you should load each new data set as a separate table.

    Try using a standard title for each column Header eg. date1 date2 etc with your actual date titles such as 2 February in row1 and actual data starting in row2.   Get and Transform in Excel 2016 has the ability to Promote first row.   So every time you load new data your Yesterday column will always be first no matter what the actual date.

    For2

    Refresh all tables will work because your history tables aren't changed at the source.

    Hope this helps.   Good luck

    Sunday, March 4, 2018 7:30 PM
  • Hi there. You should be able to do what you describe by applying cleanup and date derivation logic to each file individually before combining them (all in Power Query, not manually). If you can post more details/examples that would be helpful.

    Ehren

    Friday, March 9, 2018 12:27 AM
    Owner