Power Query 批量多维转一维,如何保留文件名?

Power Query Jeremy ⋅ 于 2018-10-10 11:25:02 ⋅ 最后回复由 Jeremy 2018-10-10 15:00:02 ⋅ 3688 阅读

我在施阳老师的博客看到这篇关于多维转一维的文章:多维转一维
但在自己尝试保留源文件文件名时出现了很多重复行,请问各位如何才能正确的保留文件名?

file

let
    fx=(bin)=>
    let
            表= Excel.Workbook(bin){0}[Data],
            转为列表 = Table.ToColumns(表),
            标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5,0,1,2,4,5,0,3,0},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9,19,19,19,19,19,26,26,27}}),each 转为列表{_{0}}{_{1}}),
        数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5,0,1,2,4,5,1,4,2},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10,20,20,20,20,20,26,26,27}}),each {转为列表{_{0}}{_{1}}}),
            结果 = Table.FromColumns(数据,标题),
            #"Added Custom" = Table.AddColumn(结果, "Custom", each Table.Range(表,10,List.PositionOf(表[Column1],"名称")-11)),
            #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6"})
    in
            #"Expanded Custom",
    Source = Folder.Files("C:\Users\Jeremy Fu\Desktop\PowerQuery\多维转一维\多维转一维\多文件汇总\源数据"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.Combine(List.Transform(#"Removed Other Columns"[Content],fx))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])), Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])))
in
    #"Expanded Custom"
成为第一个点赞的人吧 :bowtie:
最佳答案
  • Alex MOD 社区清洁工
    2018-10-10 14:33:38

    @Jeremy 那最后添加自定义列直接 fx([Content]) 就行了。
    把你的代码倒数第二步改成:

    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fx([Content]))
回复数量: 4
  • Alex MOD 社区清洁工
    2018-10-10 12:03:37

    你在自定义函数里添加列展开了一次,最后面又添加列展开了一次,当然有重复了。
    帮你改了一下:

    let
        fx=(bin)=>
        let
                表= Excel.Workbook(bin){0}[Data],
                转为列表 = Table.ToColumns(表),
                标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5,0,1,2,4,5,0,3,0},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9,19,19,19,19,19,26,26,27}}),each 转为列表{_{0}}{_{1}}),
            数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5,0,1,2,4,5,1,4,2},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10,20,20,20,20,20,26,26,27}}),each {转为列表{_{0}}{_{1}}}),
                结果 = Table.FromColumns(数据,标题)
        in
                结果,
        Source = Folder.Files("C:\Users\Jeremy Fu\Desktop\PowerQuery\多维转一维\多维转一维\多文件汇总\源数据"),
        #"Added Custom" = Table.AddColumn(Source, "a", each fx([Content])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "a"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "a", Table.ColumnNames(#"Removed Other Columns"[a]{0}))
    in
        #"Expanded Custom"
  • Jeremy
    2018-10-10 12:16:00

    @Alex 自定义函数里的那个展开,我是为了“现职称发表文章情况”有复数行时提取复数行。
    我试了一下,你帮我改的那个版本好像只能提取单行?

  • Alex MOD 社区清洁工
    2018-10-10 14:33:38

    @Jeremy 那最后添加自定义列直接 fx([Content]) 就行了。
    把你的代码倒数第二步改成:

    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each fx([Content]))
  • Jeremy
    2018-10-10 15:00:02

    @Alex 谢谢!看来我还是只知其然,不知其所以然。我再好好想想,谢谢!

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter