Save data for use later

[KDS]

I have a report that's gets exported to excel but contains a lot of information I don't need.  One piece of info that's tucked in the section I don't need is the report run date.  I need to save this for use later down in the process, after I clean up the data.

Here's a example of what I mean...

The data in black is info I don't need.  The data in blue is the info I do need.  The info in red is explained below.

I need to retain the report date so i can extract the report month.  That field will then be used as part of a join so I can pull data corresponding to that month in another table. 

The first thing I did was create a custom column with a formula that extracted the report date from the first column:

if Text.Contains([Data.Column1],"Data As Of:") then Text.Start(Text.AfterDelimiter([Data.Column1], "Data As Of: "),10) else null

Next, I did a fill down to populate the rest of the columns and then created a custom column named "Report Month"  to extract the month number only.  

The issue I'm having is that the data i need doesn't start until row 7.  To get to this, I excluded anything in Column 2 that is null.  In doing so, however, I lost the column header for the Report Month.  So when I promote headers, my header is now "8"... which obviously isn't going to work for my join as the report month will change with each update.

How do I get around this?

[AlexisOlson · Accepted solution]

I'd suggest doing this with independent steps.

Try pasting this into your Advanced Editor and looking at the applied steps one at a time.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5PC4JAEMW/yrBnwXb7Q3hTO0ZleUjEw2CTBObIth789k0qiBDMwPzmvTdMnqsrtWwdnPBNEEax8tRUhZerG3e2pAC0WXtwQIeAH+BnAHtfa9+sjF76l5RoSNlhPa4SM9NPlZujsNnu/udjdFSx7YWPXKJ7cSNj1D0qcpARWqG0b2kwhwJ36empc0tWEk01iJFsslm8YG+5rlVRfAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    DateCellText = Table.SelectRows(Source, each Text.Contains([Column1], "Data as of"))[Column1]{0},
    AsOfDate = Text.AfterDelimiter(DateCellText, "Data as of:"),
    #"Removed Top Rows" = Table.Skip(Source,6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Location", type text}, {"Budget Year", Int64.Type}, {"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ReportDate", each Date.FromText(AsOfDate), type date)
in
    #"Added Custom"

[KDS]

Thanks - with some tweaking I was able to get this to work!