英文:
Power Query / Excel tables: Auto-filling when it should not
问题
我有一个Power Query工作表，它的数据源自两个Excel表格。查询表格用于会计部门跟踪已经计费和待处理的实验室测试项目。这两个查询和工作表似乎在独立运行时都工作正常。所有在两个源表格中输入的新计费项目都能够成功地传输到查询表格中。
问题：
我添加了一个第四个表格，有两列，紧邻查询表格，用于排序。当会计已经向客户开出账单后，他们会在该列中标记一个“X”。该列已经进行了筛选，以隐藏任何“X”，因为它们表示已经完成的项目。只有未计费的项目会被会计看到。
问题出在，当从源表格中带入新的行时，它们会自动填充上一个Excel的自动填充功能中的“X”。
列A -> O 属于查询
列P和Q 属于第四个表格。
我已经禁用了自动填充和自动完成功能。
我已经禁用了一些自动更正选项。
当我删除不恰当的“X”并“刷新”数据时，一个表格中的“X”会重新出现，而另一个则不会。我不知道为什么或如何发生这种情况，因为“行项目完成”列既不是源表格的一部分，也不是查询的一部分。我不知道为什么该列会自动填充，更糟糕的是，它会根据与那些列无关的源表格进行区分。
请帮忙解决这个问题！
英文:
I have a Power Query sheet that is sourced from two Excel tables. The query table is for accounting to keep track of what lab tests have been charged and those pending. The query and sheets seem to be working fine independently. All of the new charged entered in the two source sheets get pulled over into the query just fine.
Problem:
I added a fourth table with two columns, adjacent to the Query table to use for sorting. When the accountant has billed the customer, they mark an "x" in the column. The column is filtered such that any "x" is hidden, since it is complete. Only unbilled items are seen by accounting.
What is happening is that when a new line is brought over from one of the source sheets, they are automatically filled in with an "X" with some excel autofill function.
Columns A -> O are part of the query
Columns P & Q are the fourth table.
I have disabled Autofill and Auto Complete
I have disabled some Autocorrect options
When I delete out the inappropriate X's and "Refresh" the data, the "X" from one sheet returns and from one it does not. I don't know why or how, since the "Line Item Complete" column is NOT part of either source sheet. It is not part of the query. I have no idea why the column is auto-filling,, and worse discriminate based off the source sheets that are not linked to those columns.
Please help!
答案1
得分: 1
问题实际上是在已经填入 x 的行中出现了新数据，而不是自动填充的问题。x 与其输入位置相关联，而不是与其他单元格中的数据相关联。
英文:
I think the problem here is actually that new data is appearing in rows that already have an x typed in them, not that something is being autofilled. The x is associated with the location it's typed in, not the data in the other cells.
答案2
得分: 0
解决方法是我需要创建一个自引用表。如Ricardo上面提到的，问题在于查询和我后来添加的新列之间的索引丢失。
这个表格看起来像是自动填充的原因有：
- 合并了两个工厂的两张表，它们不断添加新数据，
- 有足够的数据，行的洗牌/筛选使得列看起来像是自动填充，实际上只是“洗牌数据”。
- 当我删除了意外的自动填充后，下次刷新数据时，行就“索引”到了现在删除的单元格中，看起来像是自动填充。
为了解决这个问题，我使用了Ricardo的链接：
https://exceleratorbi.com.au/self-referencing-tables-power-query/
以及“Doug”的视频：
https://www.youtube.com/watch?v=wHgv_gWw7iQ
谢谢 -
英文:
The solution was that I needed to make a self-referencing table. As per Ricardo mentioned above, the issue was the loss of indexing between the query and the new columns I added later.
The sheet looked like it was being auto filled because:
- there merge combined two sheets from two plants that kept adding new data,
- there was sufficient data that the shuffling / filtering of the rows made it look as though the column was auto filling, when in fact, it was just "shuffling data".
- When I deleted the unexpected autofill, the next time the data was refreshed, the rows just "indexed" into the now deleted cell, looking like an autofill.
to solve the problem I used Ricardo's link:
https://exceleratorbi.com.au/self-referencing-tables-power-query/
and "Doug's" video
https://www.youtube.com/watch?v=wHgv_gWw7iQ
Thanks -
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论