Forum Discussion

ptmuldoon's avatar
ptmuldoon
Resolver I
2 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 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"
Need Help

1 Reply

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident
    2 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!