Power Query，刷新时保持输出表大小相同 [英] Power Query, keep output table same size while refreshing

问题描述

我正在使用此代码从.txt文件中过滤掉一些文本.然后在另一张纸上，我使用单元格引用来构建我的自定义表.

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.

一切正常，除非源文件为空，否则Power Query会调整输出表的大小，以便在第二张表中断开引用.

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"

推荐答案

Excel似乎将缩小的表视为删除了这些列.

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，刷新时保持输出表大小相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！