Forum Discussion

ptmuldoon's avatar
ptmuldoon
Icon for Resolver I rankResolver I
3 years ago

Add Column with Folder Path (or part of path)

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 var...
Need Help
AbhinavJoshi's avatar
AbhinavJoshi
Icon for Responsive Resident rankResponsive Resident
3 years ago

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!