☰

通过分离交易来创建复式记账表

tag-icon • tag-icon microsoft-excel sql power-query
通过分离交易来创建复式记账表

我有一张 Excel 表，其中的会计交易存储在一行上：

日期 到帐户 来自帐户 数量 类型
2023-06-01 2192 2281 100.00 美元 转移

我试图将该单行转换为复式记账交易，如下所示：

日期 到帐户 来自帐户 数量 类型
2023-06-01 2192 $-100.00 费用
2023-06-01 2281 100.00 美元 收入

我有数千个条目，需要定期更新（每月多次），所以我正在寻找一种系统的方法来重复这个过程。到目前为止，我发现的唯一解决方案是复制记录，然后使用辅助列根据行是原始行还是副本对数据进行分区。我愿意使用 Excel、Power Query、SSIS 或 SQL 解决方案（数据最终将存储在 SQL Server 数据库中）。我想避免使用 VBA。

答案1

以下是使用 Power Query 的另一种方法：

  • 在原表中添加索引列
  • 从原始表创建两个表
    • “致谢”
      • 将“发件人帐户”条目转换为空
      • 将“金额”设为负数
      • 确定“类型”：费用
    • “借记”
      • 将“收件人帐户”条目转换为空
      • 制定“类型”：收入
  • 然后
    • 重新合并两个表
    • 按索引列排序
    • 删除索引列

还可以根据帐号进行决策，以确定类型，以及金额是否应为负数。

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"To Account", Int64.Type}, {"From Account", Int64.Type}, 
        {"Amount", Currency.Type}, {"Type", type text}}),

    #"Add Index Column" = Table.AddIndexColumn(#"Changed Type","Index"),

    Credits = Table.TransformColumns(#"Add Index Column",{
        {"From Account", each null, Int64.Type},
        {"Amount", each -_, Currency.Type},
        {"Type", each "Expense", type text}}),

    Debits = Table.TransformColumns(#"Add Index Column",{
        {"To Account", each null, Int64.Type},
        {"Type", each "Revenue", type text}}),

    Results = Table.Combine({Credits,Debits}),
    #"Sorted Rows" = Table.Sort(Results,{{"Index", Order.Ascending}}),
    #"Remove Index Column" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
 
in
    #"Remove Index Column"

在此处输入图片描述

答案2

Power Query 可以轻松完成此操作。将数据放入表中以用作源，或者使用外部数据源（如 CSV 或 SQL）。

但是，如果您以类似于您帖子的格式将数据导入 PQ，则需要确保“更改类型”步骤为每列设置合适的数据类型。删除您不需要的“类型”列。

我发现此时将“金额”重命名为“原始金额”很有用，这样我以后就可以重复使用该列名。选择原始金额和日期，然后转到转换 > 取消透视列 > 其他列。

这是在只有一行的地方获得两行的关键步骤。在此之后，您需要添加一堆条件列，以便将“收件人帐户”值放入名为“收件人帐户”的列中，并将其他列的值设为空值，反之亦然。

新的“金额”列稍微有点棘手。您可以执行多个步骤并删除中间的列，或者从头开始编写自定义公式，或者介于两者之间 - 添加一个条件列，该条件列查找 To / From 并在两种情况下返回金额列。然后在公式栏中，只需通过在 [Original Amount] 前面插入“-1 *”来编辑 From 的一半。

同样，为类型（收入或支出）添加一个条件列

将金额列更改为货币数据类型（或者如果您愿意，也可以更改为小数，但固定小数通常很有用）。删除不需要的列，以便只保留您需要的列（因此删除原始金额、属性和值）。

关闭并加载 - 一切就绪！您的 M 代码（在高级编辑器中）最终将如下所示：

let
        Source = Excel.CurrentWorkbook(){[Name="StartingTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"To Account", type text}, {"From Account", type text}, {"Amount", Int64.Type}, {"Type", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Type"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Amount", "Original Amount"}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Original Amount", "Date"}, "Attribute", "Value"),
        #"Added To Account" = Table.AddColumn(#"Unpivoted Other Columns", "To Account", each if [Attribute] = "To Account" then [Value] else null),
        #"Added From Account" = Table.AddColumn(#"Added To Account", "From Account", each if [Attribute] = "From Account" then [Value] else null),
        #"Added Amount" = Table.AddColumn(#"Added From Account", "Amount", each if [Attribute] = "To Account" then [Original Amount] else -1 * [Original Amount]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Amount",{{"Amount", Currency.Type}}),
        #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Type", each if [Attribute] = "To Account" then "Expense" else "Revenue"),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Date", "To Account", "From Account", "Amount", "Type"})
    in
        #"Removed Other Columns"

相关内容