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
You could add a calculated column using:
=Date.FromText(Text.Start(Text.End(SourceFilePath, 14), 9)))
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
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:
Previous File Path step contains the desired dateColumn 1 nulls in your PM Variance Report1 with that dateyou 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.
