Add Column with Folder Path (or part of path)

[ptmuldoon]

I need to add a date column to table that is based upon the MM-YYYY being used in the file path of the file.  The file itself does not include the date.  And once I get that date into a column or variable, I am then using it for futher data/column name changes and so forth.

I've been trying with something like the below trying to get folder path, but no luck so far.   How can i set a variable or add column converting the 08-2023 in the folder path to a date of 8/31/2023?

let
    Source = Excel.Workbook(File.Contents("C:\Users\ptm\Desktop\GSS Reports\08-2023\IHG\CMHEB.xlsx"), null, true),
    Satisfaction_Sheet = Source{[Item="Satisfaction",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(Satisfaction_Sheet, "Custom", each File.Contents[Folder Path])
in
    #"Added Custom"

[AbhinavJoshi]

Hello @ptmuldoon . You can create a parameter and reference it in the source. First, create a blank query in Power Query Editor and enter the following code 

let
currentDate = Date.From(DateTime.LocalNow()),
Custom1 = {currentDate},
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Column1", type text}}, "en-CA"), "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3"}),
#"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.3"}),
Custom2 = {#"Removed Columns"}
in
Custom2

Then create a Parameter, give it an appropriate name, In the suggested values, select the query you just created, and give a current value. Example 

Now in the source, change it to Source = Excel.Workbook(File.Contents("C:\Users\ptm\Desktop\GSS Reports\" & PramCurrentMonthYear & "\IHG\CMHEB.xlsx"), null, true)

I hope it helps!