Forum Discussion

MatthewPerren's avatar
MatthewPerren
New Member
1 year ago
Solved

Use a named range in Excel for Power Query Data Source

My client needs txt files from a supplier converted to excel. I've created a PQ to do that but I'd like to make life easy for them so I've added some VBA which allows them to look up the file to b...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

To get the contents of a file as Source, you use:

File.Contents(full_pathname)

 

Of course, you also have to properly parse the file and depending on the file type, you might use Csv.Document, Json.Document, Xml.Document or some custom connector.

  • MatthewPerren's avatar
    MatthewPerren
    New Member
    1 year ago

    So you can't use a pathway stored in another query? It HAS to be the full pathway?

     

    • MatthewPerren's avatar
      MatthewPerren
      New Member
      1 year ago

      So...after a few hours of trail and error, it would seem that the answer to my question is, "No", you cannot.
      The error message shown in the previous screenshot: Formula.Firewall: Query 'BLAE3685 DD' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Is saying that, if the query bringing the named range into power query has any steps, it may not be quoting a full path and there doesn't seem to be a way of directly accessing the named range in the workbook from the Power Query editor.

      So if you directly enter the file path into a parameter you can use it just fine. But if the parameter is picking the file path up from a query then it won't accept it for the reason above.

      Very frustrating. 

      If anyone knows how I can get arpund this, I'd be really interested.

      Thanks