Carrying a date field down in a column until a new date is reached

[Dan_at_TWE]

I reading several financial PDFs from a folder into power query. The first couple of lines of each PDF has the date the financial statement covers in the first few lines of the PDF header.  (From QuickBooks if it maters...)

As you can see if you squint (LOL),  the column in the middle has the string "As of" with the date.  Futher down the imported data that date of course changes, to another period. AKA the next PDF report.

Before I use row filtering or whatever to remove the report header data I don't need, I would like to use that "As of" string to put together a date column that fills down to subsequent rows. When the power query hits the next "As of" date, fill down using the new date, and so on.

Wondering how one would accomplish this... 

[vojtechsima]

Hey, @Dan_at_TWE ,

here's the code for it, copy paste it to blank query and then take the steps:

1. Make sure Null is type null (replace "null" with null)
2. Simply fill down by that column
3. Transfrom or Create new column with lowering, trimming and extracting date

This will work in your blank query if you copy it 1:1, the first step is just generating the sample data.

let
    source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZDBCsIwDIZfJfTcQ9YheO12V1BvY4euRBS7Zthu4NurU+eUCbt4CuT7Q36+ohC+dU5IoQPwHtY2ckVnSBMJClV6Az0v5RAcj/tah0AxTIDM+BNoa7n1Y54sEPFx+WSwIUvHzlSO3rEhteNoHHx/UfjiP2rlXDfGX0Bn+WQ3Z7wl2B6I4gT/kLLijureCv7PipplZTnLSnkF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    replacedValue = Table.ReplaceValue(
        source,
        "null",
        null,
        Replacer.ReplaceValue,
        {"Column1", "Column2", "Column3"}
    ),
    filledDown = Table.FillDown(
        replacedValue,
        {"Column2"}
    ),
    custom1 = Table.TransformColumns(
        filledDown,
        {
            {
                "Column2", 
                each Date.From(Text.AfterDelimiter(Text.Lower(_), "as of "), "en-US"), 
                type date
            }
        }
    )
    in
        custom1

[v-menakakota]

Hi @Dan_at_TWE ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @vojtechsima   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team

[danextian]

You should be able to use fill down assuming that all other rows in that column are null until the next "as of date". After that, you can replace "As of " with nothing and then you can parse the column as date.

[DanieleUgoCopp · Accepted solution]

Hello,
I would try  to add a conditional column that checks if the text contains “As of”, if yes extract the date part, otherwise return null, then use Fill Down on that column so the date propagates until the next “As of” row appears, after that you can filter out the header rows you don’t need and keep the filled date column attached to the data rows.

If the date is embedded in the same column as other text you might need a small Text.AfterDelimiter or similar step to isolate the date first, but the general idea is detect the “As of” rows, create the date, then fill down.
Best regards,
Daniele

[Dan_at_TWE]

I ended up doing something similar.