电源查询 - 复杂的自定义列

Question

我需要创建一个自定义列，该列查看1列，a，并基于a，将b的值调整为一个值，然后将值放在c中。

b part b：现在，如果a中的值与我的标准不匹配，则它查看d的列以查找它是否与上一行相同，如果是，则将c的值从上一行放入新行。

我现在有一部分：

if Text.End ([SAP segment name],3) = "K09"
then Text.Trim (Text.Start([Application data],35)) else ""

我需要B部分的帮助

Answer 1

您的公式[b3 = if(right(r3,3(=" k09"，trim(w3,35(，b2(，b2(，]将返回每个行的错误，直到R首次以K09结束。但是，如果那是您想要的，那么

let Source = Table.FromList({
[SAP segment name="L04K29", Application data ="ABCDEFGHIJKLMNOP12345VWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="R03L09", Application data ="abcdefghijklmnop67890qrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOP987654QRSTUVWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOPQR321254STUVWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="R03G09", Application data ="abcdefghijklmnopqrst87562uvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOPQRSTU45265VWXYZabcdefghijklmnopqrstuvwxyz"] 
}, Record.FieldValues, {"SAP segment name", "Application data"}),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.End ([SAP segment name],3) = "K09" then Text.Trim(Text.Start([Application data],35)) else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
#"Filled Down"