none
Using unique source from file RRS feed

  • Question

  • I'm working on automating a report.  The data source that I need to pull is delivered on a half hour interval throughout the day.  Currently I have my query set up to find the file that it needs.  The error that I'm running into is that my query generates an error after the step where I select the binary on the half hour report.

    After looking at the data, I've recognized that the issue is the names of the report don't match.  they are only slightly different but this seems to cause an issue with the importing CSV portion.  Does anyone know a way to use a wildcard in the name of the file portion of the formula in M so that I can only look at the portion of the file name that isn't unique to the hour or half hour?

    Monday, May 19, 2014 8:34 PM

Answers

  • Yes, this should be possible, but you'll have to manually edit the formulas a bit. Have you done anything with our formula language yet? Here's a rough sketch of what I'm thinking. This answer assumes that a new file gets created in the directory every 30 minutes. Our solution will just grab the most recent one and operate on that. 

    1. First, make sure your formula bar is enabled. In the Query Editor, click the View tab and check the box next to Formula Bar. Close the editor.
    2. Now back in Excel, use the From Folder data source and point that to the folder containing all the of the CSV files.
    3. Sort that table to have the most recent file on top.
    4. Right click on the Binary field for that top row and choose Drill Down.
    5. If that was a CSV file, then a bunch of steps got automatically added. You can see them on the right side of the query editor. Click back on the step right after you finished sorting your table in step #3 of this post. The name of the step is probably the file name.
    6. Now that you have the step selected, look at the formula bar. It probably looks something like this:
      = SortedRows{[#"Folder Path"="C:\Users\Ben\SkyDrive\Documents\",Name="test.csv"]}[Content]
      We need to strip out the specific path information and instead, use the first row which is index 0. Change the formula like this:
      = SortedRows{0}[Content]
      Press Enter to commit the change. Hopefully you don't see an error.
    7. Now go back to the last step in the Applied Steps list on the right side of the Query Editor. If you don't see any errors then I think you're good to go. Click the Refresh button in the Home tab of the Query Editor ribbon just to make sure it's all working.

    Now when new files get added to that folder, it will always grab the most recent one.

    Since I don't know your exact scenario, there are lots of places where my instructions might have diverged from what you actually need. Please keep in touch if this doesn't exactly solve your problem!

    Monday, May 19, 2014 9:49 PM
    Moderator