如何使用PQ在文本数据找出对应的其他数据并提取出来

bet007

求助.zip (15.5 KB, 下载次数: 28)

2023-8-22 10:10 上传

点击文件名下载附件

具体需求在附件,请高手帮忙解决。谢谢！

kirete

bet007

kirete 发表于 2023-8-22 10:24

我是试过使用你的代码，好像不行啊！

dambcer

再操作下拆分列就行了

minchar

= Table.AddColumn(源, "自定义", each   Text.Combine(Table.SelectRows(Table.FromColumns( {Text.Split([销售明细],",")  , Text.Split([店号],",")}),(x) => x[Column1] <> "0")[Column2],","))

hbzql

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    删除的列 = Table.RemoveColumns(源,{"模拟数据", "模拟数据2"}),
    筛选 = Table.AddColumn(删除的列,"筛选",each [a=List.Zip({Text.Split([销售明细],","),Text.Split([店号],",")}),b=List.Transform(a,each if Number.From(_{0})>0 then _{1} else null),c=List.RemoveNulls(b)][c]),
    自定义1 = Table.SplitColumn(筛选,"筛选",each _,3)
in
    自定义1

maoxiong1999

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    a = Table.Combine(Table.AddColumn(源,"t",each [x=Table.SelectRows(Table.Transpose(Table.FromList(
        {[销售明细],[店号]}),{"m","n"}),each [m]>"0")[[n]],y=Table.FromRecords({_&Table.ToRecords(
        Table.Transpose(x,List.Transform({1..Table.RowCount(x)},each Number.ToText(_,"店0")))){0}})][y])[t])
in
    a
C:\Users\maox\Desktop\QQ图片20230825121553.png

尘土1

1. 已添加自定义 = Table.AddColumn(删除的其他列, "自定义", each Table.FromRows({Table.SelectRows(Table.FromRows(List.Zip({Text.Split([销售明细],","),Text.Split([店号],",")})),(x)=>x[Column1]<>"0")[Column2]})),
   
2.    展开 = Table.ExpandTableColumn(已添加自定义, "自定义", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
   
3. in

复制代码

pxy0409

不精通M函数,用纯菜单操作,分多步也可以实现