Add Date of Report to Column using Power Query - Excel

Question

Not sure if this will make sense. I have been Googling and trying to find the answer to this but cannot work it out, I am trying to add in a custom formula to the Power Query that will add in the date of the report to each row item within the report:

Replacing the "Null" with the Report Name Date

This is the source and the last 8 characters are the date

Does anyone have any clue on how to achieve this? I have been stuck on this for a few hours

Thanks, Shea

Answer 1

You could add a calculated column using:

=Date.FromText(Text.Start(Text.End(SourceFilePath, 14), 9)))

Answer 2

try

path = Excel.CurrentWorkbook(){[Name="StartOfMonthFilePath"]}[Content]{0}[Column1],
mydate = Text.End(Text.BeforeDelimiter(path , ".", {0, RelativePosition.FromEnd}),9),
add = Table.AddColumn(NameOfYourTableHere, "NewColumnNameHere", each mydate)
in add

Answer 3

It's a little hard to tell exactly what you want. First of all, the screenshot tagged with This is the source is actually showing the result of the preceding step.

And your screenshot of your table does not show where exactly you want the dates.

Assuming:

• The result of your Previous File Path step contains the desired date
• You want to replace Column 1 nulls in your PM Variance Report1 with that date

you can use the following code inserted after PM Variance Report1:

   #"Extract Date" = Date.From(Text.End(Text.BeforeDelimiter(#"Previous File Path",".",{0,RelativePosition.FromEnd}),9),"en-US"),
    #"Replace Col1 Nulls" = Table.ReplaceValue(
        #"PM Variance Report1",
        null,
        #"Extract Date",
        Replacer.ReplaceValue,
        {"Column1"})

Before

After

You'll also need to change the code of your subsequent step (probable Changed Type to refer to #"Replace Col1 Nulls" step rather than PM Variance Report 1.