PowerQuery : Retrieve table data from all files in a variable folder

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
Hi,

I've just started dipping my toe into the world of PowerQuery and M and I can see the potential of what it can do ... however my skills aren't quite there yet !

I have a folder called Weekly Reports that will contain folders full of xlsx files saved by weeks eg :
2018-05-21
2018-05-28
2018-06-04

In each of these folders is a number of xlsx files each with an identical tab called League with a table called LeagueTableData. I have created this query to return a list of all the folders (found on http://radacad.com/fetch-files-andor-folders-with-filtering-and-masking-power-query) :
Rich (BB code):
let
    Source = Folder.Contents("Y:\XXXXXX\Weekly Reports"),
    #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Kind"}, {"Attributes.Kind"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Attributes",{"Name", "Attributes.Kind", "Folder Path", "Date created"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Name] <> "Not Relevant1" and [Name] <> "Not Relevant2")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}})
in
    #"Sorted Rows"

I'm using this result as the source of a dropdown so my end user can choose the latest week to work with.

This is the query to return the information I need
Rich (BB code):
let
    Source = Folder.Files("Y:\XXXXXX\Weekly Reports\DevTest\2018-06-04"),
    #"Uppercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Upper, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each Text.Contains([Extension], "XLS")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetData", each functionGetFiles([Folder Path],[Name])),
    #"Expanded GetData" = Table.ExpandTableColumn(#"Added Custom", "GetData", {"BASELINE USAGE", "OPENING/CLOSING USAGE", "OPERATIONAL USAGE", "WASTE USAGE", "WASTE COST", "%"}, {"BASELINE USAGE", "OPENING/CLOSING USAGE", "OPERATIONAL USAGE", "WASTE USAGE", "WASTE COST", "%"})
in
    #"Expanded GetData"

How can I get the variable of "2018-06-04" to be replaced with a variable chosen via a dropdown ?

I've tried to build a filepath and assign a named reference to it as found here https://powerbi.tips/2016/08/using-variables-for-file-locations/ however I can't get it to work with concatenation. I'm also not using PowerBI so I wonder if this is the problem

I've tried to use this method https://www.howtoexcel.org/power-query/how-to-import-all-files-in-a-folder-with-power-query/ however it too seems to end up with a with a hardcoded folder name that I can't swap out for a variable path when I use it.

I feel like I'm close, but don't have the knowledge to bring it home
Any advise or resources are more than welcome (I received my copy of M is for Data Monkey this morning ;) )
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You can create a table in your workbook which can hold the path (I named the table Table 1)

AKpskxt.png




Then in M you can do:

FolderPath: = Text.From(Excel.CurrentWorkbook(){[Name="[B]Table1[/B]"]}[Content]{0}[Folder])

Source = Folder.Files("Y:\XXXXXX\Weekly Reports\DevTest" & FolderPath)
 
Upvote 0
Thank you so much for your quick reply.
I must be doing something wrong as I wrote a new basic query :
Code:
let
    FolderPath: = Text.From(Excel.CurrentWorkbook(){[Name="Week1Table"]}[Content]{0}[Folder])    
    Source = Folder.Files("Y:\\Weekly Reports\DevTest"& FolderPath)
in
    Source

I get the error Expression.SyntaxError: Token Equal expected.

I tried Source = Folder.Files("Y:\XXXXXX\Weekly Reports\DevTest" & FolderPath) as well incase it was the trailing slash that was missing.

Same error.

When I close and load I get the error [Expression.Error] The name 'Source' wasn't recognized. Make sure it's spelled correctly.

Thank you again - any further advise is welcome
 
Upvote 0
There were two typos on my previous post

try to do:

Code:
[B]FolderPath = Text.From(Excel.CurrentWorkbook(){[Name="[B]Table1"]}[Content]{0}[Folder])

Source = Folder.Files("Y:\XXXXXX\Weekly Reports\DevTest\" & [B]FolderPath)[/B][/B][/B]

I have removed the : after FolderPath and I added the path separator after DevTest


Thank you so much for your quick reply.
I must be doing something wrong as I wrote a new basic query :
Rich (BB code):
let
    FolderPath: = Text.From(Excel.CurrentWorkbook(){[Name="Week1Table"]}[Content]{0}[Folder])    
    Source = Folder.Files("Y:\\Weekly Reports\DevTest"& FolderPath)
in
    Source

I get the error Expression.SyntaxError: Token Equal expected.

I tried Source = Folder.Files("Y:\XXXXXX\Weekly Reports\DevTest" & FolderPath) as well incase it was the trailing slash that was missing.

Same error.

When I close and load I get the error [Expression.Error] The name 'Source' wasn't recognized. Make sure it's spelled correctly.

Thank you again - any further advise is welcome
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top