英文:
Power Query - Find matching contents from multiple other tables
问题
我有一组数据，规模不小，我正试图在Power Query中进行转换。其中一个列（比如，"Column_1"）的值包含了几个维度的数据，这些数据在任何情况下都没有一致的分隔符。我想对这一列应用公式，以执行以下操作：
1）参考各个单独的表（比如，"Lookup_n"），每个表都列出了给定维度的所有可能值，确定是否在列1的数据中存在表中包含的子字符串
2）如果存在，将该子字符串插入到与该维度相关的新列中，并从列1的数据中删除它
以下是我希望发生的示例：
我对Power Query相当陌生，所以不太清楚如何开始制定这个问题的解决方案。如果有比我描述的方法更容易实现这一目标的方法，我会非常感兴趣听到。
谢谢！
英文:
I have a set of data of non-trivial size that I am trying to transform in Power Query. One column's (say, "Column_1") values holds several dimensions of data that are not consistently delimited in any way. I want to apply formulas to this column to do the following:
- with reference to various separate tables (say, "Lookup_n") each listing all possible values for a given dimension, identify whether a substring contained in a table is present in the data in Column1
- if it is present, insert that substring into a new column specific to that dimension, and remove it from the data in Column1
Here is an example of what I would like to have happen:
I am fairly new to Power Query so don't really know where to begin in formulating a solution to this. I would be very interested to hear if there is an easier way to accomplish this than using the method I have described.
Thanks!
答案1
得分: 0
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lookup = Table.UnpivotOtherColumns(Table.Combine({lookup_3, lookup_2, lookup_1}), {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(Source, "custom", (i) => (Table.SelectRows(Lookup, each Text.Contains(i[Column_1], [Value])))),
Expanded = Table.ExpandTableColumn(#"Added Custom", "custom", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Column_1", type text}, {"Attribute", type text}, {"Value", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"<none>",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"<none>"})
in
#"Removed Columns"
英文:
In powerquery, try this code for the input after creating query lookup_1 (with column name lookup_1), query lookup_2 (with column name lookup_2_ and query lookup_3 (with column name lookup_3)
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lookup = Table.UnpivotOtherColumns( Table.Combine({lookup_3, lookup_2, lookup_1}),{} , "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(Source,"custom",(i)=>(Table.SelectRows(Lookup, each Text.Contains(i[Column_1],[Value])))),
Expanded = Table.ExpandTableColumn(#"Added Custom", "custom", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Column_1", type text}, {"Attribute", type text}, {"Value", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"<none>",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"<none>"})
in #"Removed Columns"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论