Power Query, keep output table same size while refreshing

Question

I'm using this code to filter some text out of a .txt file. Then in another sheet i use cell references to built my custom table.

Everything is working great, except if the source file is empty, Power Query resizes the output table so that the reference got broken in the second sheet.

=cellref!$C4

how can I prevent power query to resize the range in his table? or, somehow, keep the same number of rows/column so that it won't break my references?

    let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\ceusebio\OneDrive - Hypertec\RESULTS\LogHoudini.txt"), null, null, 1252)}),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("Generating Image: /home/ciara/Documents/RESULTS/Houdini/images/img.jpg (", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByDelimiter("SampleFilter: ", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column1.1.1", Splitter.SplitTextByDelimiter("Thread Count: ", QuoteStyle.Csv), {"Column1.1.1.1", "Column1.1.1.2", "Column1.1.1.3", "Column1.1.1.4", "Column1.1.1.5", "Column1.1.1.6", "Column1.1.1.7"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column1.1.1.1", Splitter.SplitTextByDelimiter("] Frame Wall Clock Time: ", QuoteStyle.Csv), {"Column1.1.1.1.1", "Column1.1.1.1.2"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter3", {{"Column1.1.1.1.1", each Text.BeforeDelimiter(_, " dop1"), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1.2", each Text.BeforeDelimiter(_, ")"), type text}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Extracted Text Before Delimiter1", "Column1.2", Splitter.SplitTextByDelimiter("x", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter4",{"Column1.1.1.1.1", "Column1.1.1.1.2", "Column1.1.1.2", "Column1.1.2", "Column1.2.1", "Column1.2.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.1.1.1.1", type time}})
in
    #"Changed Type"

What do your formulas look like? (The ones producing #REF!.) — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-25T21:35:27

It appears that Excel is treating a shrunken table as if those columns were deleted.

It's not a great solution but you can wrap the cell reference in an INDIRECT as a workaround since this treats Houdini!$C4 as a constant text value rather than a direct cell reference.

=INDIRECT("Houdini!$C4")

Power Query, keep output table same size while refreshing

1 Answers