Power Query 实战案例第 4 期 不规范业务人员信息多列相同标题数据清洗!

能下载附件就好了!

@天堂蛮 蓝色的好好学习天天向上就是附件

let
Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(Source, {"日期"}, "属性", "值"),
Custom1 = Table.FromColumns({List.Alternate(Unpivoted[日期],1,1)}&List.Transform({1,0}, each List.Alternate(Unpivoted[值],1,1,_)),{"日期","业务员","销售额"})
in
Custom1

自定义1 = List.Transform(Table.ToRows(源),each List.RemoveNulls(_)),
自定义2 = Table.Combine(
    List.Transform(自定义1,
        each Table.FromColumns(
            { {_{0}} } & 
            List.Zip(
                List.Transform(
                    {1..(List.Count(_)-1)/2},
                    (x)=>List.Range(
                        _,
                        x*2-1,
                        2
                    )
                )
            )
        )
    )
)