Excel Power Query 中的复杂转换 [英] Complex transformation in Excel Power Query
问题描述
我有 2 个输入表.输入表1是源数据，输入表2是标准表.
I have 2 input Tables. Input Table1 is the source data and input Table 2 is a criteria table.
+--------------------------+----------+ +--------------------------+-------+
| TABLE 1 (Source data) | | TABLE 2 (Criterias) |
+-------------------------------------+ +----------------------------------+
+-------------------------------------+ +----------------------------------+
| DESCRIPTION | VALUE | | PREFIX | CODE |
+-------------------------------------+ +----------------------------------+
| ID | 0 | | 7235 | ABX1 |
| NAME | JFMSC | | 3553 | POWQ |
| TYPE | UHELQ | | 7459 | UWEER |
| DFRUL | F4 | | 10012 | ABX1 |
| ADDR | 10012002 | | 430 | ABX1 |
| RRUL | P1 | +--------------------------+-------+
| ADDR | 723 |
| RRUL | P1 |
| ID | 2 |
| NAME | PLLSJS |
| TYPE | UHELQ |
| DFRUL | P3 |
| ID | 4 |
| NAME | AAAARR |
| TYPE | UHELQ |
| DFRUL | T7 |
| ADDR | 35531156 |
| RRUL | P1 |
| ADDR | 72358 |
| RRUL | P1 |
| ADDR | 86401 |
| RRUL | K9 |
| ID | 0 |
| NAME | PPROOA |
| TYPE | RRHN |
| DFRUL | P1 |
| ADDR | 43001 |
| RRUL | T8 |
| ADDR | 7459001 |
| RRUL | D4 |
| ADDR | 430457 |
| RRUL | W2 |
| ADDR | 745913 |
| RRUL | P1 |
| ADDR | 74598001 |
| RRUL | Y5 |
+--------------------------+----------+
我的目标是获得如下所示的输出表(将是表 #4)，即显示了与基于表 2"的标准的字段ADDR"的每个数量相比最相似的代码.如果每个ID都有重复的CODE，我只想展示一个(唯一代码列表).
My goal is to get the an output table like below (Would be the Table #4), that shows the CODE that is THE MOST similar compared with each number of field "ADDR" based on criterias of "TABLE 2". If there are repeated CODEs for each ID, I only want to show one (unique codes list).
我在此处附加的示例文件中进行了更详细的解释 SampleV1.xlsx.
I explain in more detail in Sample file attached here SampleV1.xlsx.
我想转换基于输入表 1 和 2 中的数据以获得这样的输出表(附加文件中的所需输出表 #2):
I want to Transform the data based in Input Table 1 and 2 to get an output table like this (Desired OUTPUT TABLE #2 in file attached):
+----+--------+-------+-------+-------+------+
| ID | NAME | TYPE | DFRUL | CODE | RRUL |
+----+--------+-------+-------+-------+------+
| 0 | JFMSC | UHELQ | P1 | ABX1 | P1 |
| 2 | PLLSJS | UHELQ | P3 | | |
| 4 | AAAARR | UHELQ | T7 | POWQ | P1 |
| | | | | ABX1 | P1 |
| | | | | 86401 | K9 |
| 0 | PPROOA | RRHN | P1 | ABX1 | P1 |
| | | | | UWEER | P1 |
+----+--------+-------+-------+-------+------+
我希望有人能帮我解决这个问题.提前致谢.
I hope someone could help me with this. Thanks in advance.
推荐答案
以下是更新后的解决方案.
Below is the UPDATED solution.
总的来说，我编译了解决方案是为了尽可能减少数据问题的影响.
In general, I compiled the solution in order to be as less vulnerable to problems with data, as possible.
对数据的唯一限制是:
字段集合必须有 ID 字段，ID 字段必须是集合的第一个字段.
Field sets must have ID field, which must be the first field of set.
所有的 RRUL 和 ADDR 必须成对，
all the RRUL and ADDR have to be in pairs,
一个 ID 内的 RRUL/ADDR 对重复是可以接受的，也可以不存在.
Duplicates of RRUL/ADDR pairs inside one ID are acceptable or absent.
我还以某种方式编译了解决方案，以便在 ADDR 和 PREFIX 的所有可能变体中正确找到最接近的值.顺便说一句 - 有一种情况，没有包含在你的大样本中 - 当 PREFIX 比 ADDR 短但不等于它时.如果存在此类情况 - 我的解决方案会正确处理它们，但需要针对这种特定情况进行一些性能开销.
I also compiled the solution in a way to correctly find the closest value in all possible variants of ADDR and PREFIX. By the way - there is one case, not covered in your bigsample - when PREFIX is shorter then ADDR but not equal to it. If there are such cases - my solution handles them correctly but demands some performance overhead for this particular situation.
let
Source = #"Source data",
#"Added Index1" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Main Key", each if [DESCRIPTION] = "ID" then [Index] else null, type number),
#"Added Custom10" = Table.AddColumn(#"Added Custom", "Last notADDR", each
if [DESCRIPTION] <> "ADDR" and [DESCRIPTION] <> "RRUL" then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom10",{"Main Key", "Last notADDR"}),
#"Added Custom2" = Table.AddColumn(#"Filled Down", "Key", each [Main Key] + (
if [DESCRIPTION] = "RRUL" then [Index] - [Last notADDR] - 2
else if [DESCRIPTION] = "ADDR" then [Index] - [Last notADDR] - 1 else 0)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Main Key", "Last notADDR"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns",
List.Distinct(#"Removed Columns"[DESCRIPTION]), "DESCRIPTION", "VALUE"),
#"Added Custom3" = Table.AddColumn(#"Pivoted Column1", "CODE", each if [ADDR] = null then null else let t = Table.AddIndexColumn(Table.SelectRows(Criterias, (x)=>
let s=List.Sort({x[PREFIX], [ADDR]}, each Text.Length(_)) in Text.StartsWith(s{1}, s{0})), "Index")
in if Table.RowCount(t) > 0 then Table.First(Table.Sort(t, (y)=> Number.BitwiseShiftLeft(Number.Abs(Text.Length([ADDR]) - Text.Length(y[PREFIX])), 16) + y[Index]))[CODE]
else "Not Found"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Key", "ADDR"}),
#"Filled Down1" = Table.FillDown(#"Removed Columns1",{"ID", "NAME", "TYPE", "DFRUL"})
in
#"Filled Down1"
这篇关于Excel Power Query 中的复杂转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
