各位老师如何使用power query将表格根据日期将纵向表横向展示

maoxiong1999

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    a = Table.UnpivotOtherColumns(源, {"日期", "单位"}, "t","n"),
    b = Table.CombineColumns(a,{"日期","t"},each _{0}&(if Text.End(_{1},1)="量" then "量" else "收"),"m"),
    c = Table.Pivot(b, List.Distinct(b[m]), "m", "n", List.Sum),
    d = Table.Sort(c,each List.PositionOf(源[单位],[单位])),
    e = Table.ReorderColumns(d,List.Combine(List.Transform(List.Sort(List.Distinct(源[日期]),
        each Number.From(Text.Remove(_,"月"))),each {_&"量",_&"收"})))
in
    e
C:\Users\PC01\Desktop\QQ图片20230915132348.png

pxy0409

尘土1 发表于 2023-9-13 11:06

步骤简化了不少,简单易上手,值得学习!

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视的其他列 = Table.UnpivotOtherColumns(源, {"日期", "单位"}, "属性", "值"),
    合并的列 = Table.CombineColumns(逆透视的其他列,{"日期", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"已合并"),
    排序的行 = Table.Sort(合并的列,{{"已合并", Order.Ascending}}),
    已透视列 = Table.Pivot(排序的行, List.Distinct(排序的行[已合并]), "已合并", "值", List.Sum)
in
    已透视列

maoxiong1999

pxy0409 发表于 2023-9-22 16:58
步骤简化了不少,简单易上手,值得学习!

let

没有按月排序，属于半成品