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

[Nyanko]

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) :

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

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 )

 

[VBA Geek]

You can create a table in your workbook which can hold the path (I named the table Table 1)

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)

 

[Nyanko]

Thank you so much for your quick reply.
I must be doing something wrong as I wrote a new basic query :

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

 

[VBA Geek]

There were two typos on my previous post

try to do:

[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 :

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