英文:
Power Query ; Determine in which row and column a certain string appears in a table
问题
让我们假设我有下面这张表格：
| 列 1 | 列 2 | 列 3 | 列 4 | 列 5 |
|---|---|---|---|---|
| Hello | ||||
你能帮我找出确定“Hello”出现的行（奖励问题：出现在哪一列）的 Power Query 步骤吗？在我的示例中，它在第2行（第4列），但行数和“Hello”的位置都可能不同。
谢谢。
我一直没有找到解决方案。
英文:
Let's say I have this table below:
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 |
|---|---|---|---|---|
| Hello | ||||
Could you help me figuring out the Power Query steps to determine the row (bonus question: what column) where "Hello" appears? IN my example in would be in row 2 (and column 4), but the number of rows can vary and the location of the "Hello" too.
Thanks
I haven't been able to find a solution.
答案1
得分: 1
添加列...索引列
右键单击索引列并取消轴旋转其他列
过滤剩余的列以查看是否包含您的文本，使用列顶部的箭头
剩下的是行号和出现的列名
英文:
Add column ... index column
Right click the index column and unpivot other columns
Filter the remaining column to see if contains your text using the arrow atop the column
What's left is the row number and the column name it appeared in
答案2
得分: 1
以下是翻译好的部分：
Thought it would be fun to try to make this from scratch with some m-code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lst = List.Transform(Table.ToList(Source), (x)=> let y=List.PositionOf(Text.Split(x, ", "), "Hello")+1 in if y>0 then y else null),
Col = List.RemoveNulls(Lst){0},
Row = List.PositionOf(Lst, Col)+1
in
Row
//Col
Depending on what you are after you can change 'Row' to 'Col' under 'in'. Also, note that you can change the delimiter for `Table.ToList()` with a 2nd parameter `Combiner.CombineTextByDelimiter("|")` if need be. Make sure to change the delimiter in `Text.Split()` to the same.
英文:
Thought it would be fun to try to make this from scratch with some m-code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lst = List.Transform(Table.ToList(Source), (x)=> let y=List.PositionOf(Text.Split(x,","), "Hello")+1 in if y>0 then y else null),
Col = List.RemoveNulls(Lst){0},
Row = List.PositionOf(Lst, Col)+1
in
Row
//Col
Depending on what you are after you can change 'Row' to 'Col' under 'in'. Also, note that you can change the delimiter for Table.ToList() with a 2nd parameter Combiner.CombineTextByDelimiter("|") if need be. Make sure to change the delimiter in Text.Split() to the same.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论