以连贯的方式组织发票报告中的费用

Question

我有一份糟糕的发票报告，其中附加费的明细如下：

我想要的输出（如果有更智能的输出，欢迎提出建议）是可以看到交易号码，参考号码以及与之相关的所有类型的费用，如下所示：

有人可以提供辅助列或宏或 Get-And-Transform 等方面的帮助吗？

Answer 1

这是一个Power Query解决方案：

• Data--> Get & Transform --> From Table/Range
• 将下面的 M 代码粘贴到Advanced Editor
• 在第 2 行中，将表的名称更改为反映数据的实际表名
• 如果将鼠标悬停在“应用步骤”窗口中的条目上，和/或双击齿轮图标，您将了解正在发生的事情。

算法

• 为列创建“标签”列，EN, Tax, and Total使格式与代码类型列一致
• 除前两列外，其余列取消透视，以创建垂直数组
• 添加一列，使其Charge Amount与位于同一行Charge Type
• 过滤掉不需要的行并删除不需要的列

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    
    //create label columns for EN, Tax and Total
    addEN = Table.AddColumn(Source, "EN Label", each "EN"),
    addTax = Table.AddColumn(addEN, "Tax Label", each "Tax"),
    addTotal = Table.AddColumn(addTax, "Total Label", each "Total"),

    //Reorder the columns
    reOrder = Table.ReorderColumns(addTotal,
                List.Combine({List.RemoveLastN(Table.ColumnNames(Source),3),  
                            List.Combine(List.Zip(
                                {List.LastN(Table.ColumnNames(addTotal),3),
                                List.LastN(Table.ColumnNames(Source),3)
                            }))
                        })
    ),

    //replace nulls with blanks so we don't lose them during unpivot
    blankIt = Table.ReplaceValue(reOrder,null,"",Replacer.ReplaceValue,Table.ColumnNames(reOrder)),

    //Unpivot other than first two columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(blankIt, {"Transaction", "Reference"}, "Attribute", "Charge Type"),

    //Move Amount to same row as the Service Code
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Charge Amount", each if [Charge Type] = "" then null 
else if Number.Mod([Index],2) = 0 then 
    #"Added Index"[Charge Type]{[Index]+1}
    else null),

    //filter out unneeded rows
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Charge Amount] <> null)),

    //remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Transaction", type text}, {"Reference", type text}, {"Charge Type", type text}, {"Charge Amount", Currency.Type}})
in
    #"Changed Type"

原始数据

s

结果

笔记：稍加修改，您可以在每个交易组之间添加一个空白行

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    //create label columns for EN, Tax and Total
    addEN = Table.AddColumn(Source, "EN Label", each "EN"),
    addTax = Table.AddColumn(addEN, "Tax Label", each "Tax"),
    addTotal = Table.AddColumn(addTax, "Total Label", each "Total"),

    //Reorder the columns
    reOrder = Table.ReorderColumns(addTotal,
                List.Combine({List.RemoveLastN(Table.ColumnNames(Source),3),  
                            List.Combine(List.Zip(
                                {List.LastN(Table.ColumnNames(addTotal),3),
                                List.LastN(Table.ColumnNames(Source),3)
                            }))
                        })
    ),

    //replace nulls with blanks so we don't lose them during unpivot
    blankIt = Table.ReplaceValue(reOrder,null,"",Replacer.ReplaceValue,Table.ColumnNames(reOrder)),

    //Unpivot other than first two columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(blankIt, {"Transaction", "Reference"}, "Attribute", "Charge Type"),

    //Move Amount to same row as the Service Code
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Charge Amount", each if [Charge Type] = "" then null 
else if Number.Mod([Index],2) = 0 then 
    #"Added Index"[Charge Type]{[Index]+1}
    else null),
    //filter out unneeded rows
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Charge Amount] <> null)),

    //remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Transaction"}, {{"Grouped", each _, type table [Transaction=nullable text, Reference=nullable text, Charge Type=nullable text, Charge Amount=nullable number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "addBlankRow", each Table.InsertRows([Grouped],
  Table.RowCount([Grouped]), 
    {Record.FromList(
       List.Repeat({null}, Table.ColumnCount(#"Removed Columns")),
       Table.ColumnNames(#"Removed Columns"))})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Transaction", "Grouped"}),
    #"Expanded addBlankRow" = Table.ExpandTableColumn(#"Removed Columns1", "addBlankRow", {"Transaction", "Reference", "Charge Type", "Charge Amount"}, {"Transaction", "Reference", "Charge Type", "Charge Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded addBlankRow",{{"Transaction", type text}, {"Reference", type text}, {"Charge Type", type text}, {"Charge Amount", Currency.Type}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1)
in
    #"Removed Bottom Rows"