oonyanya/revert_powerbi.md

## revert_powerbi.md

      
    Raw
  

              revert_powerbi.md
            
          
    引当金との打ち消し処理

処理の流れ

考え方としてはテーブルのデーターを集計対象の取得で取得し、このテーブルを１とする。
テーブル１のデーターを引当金と対応するサブ明細でグループ化し、グループ化したテーブルを２とする。
そのうえでテーブル１の金額に-1をかけ、Table.TransformRowsで取得したテーブル２をTable.InsertRowsで挿入しているだけである。
なお、金額を-1しているのは元データーの明細が収入の時だけ金額が-1になっているのをプラスにしたいからである。
前提条件

エクセル
「日付、明細、金額、サブ明細、列1,ラベル」という項目のデーターが存在すること
このデーターをテーブル化しておくこと
PowerQueryの処理内容

集計対象の取得
let
    // ブック内テーブルと名前付き範囲をリスト取得
    TblSource = Excel.CurrentWorkbook(),
    フィルターされた行1 = Table.SelectRows(TblSource, each ([Name] <> "'1月'!ExternalData_1" and [Name] <> "'０３月'!_FilterDatabase" and [Name] <> "'０９月'!_FilterDatabase" and [Name] <> "'３月'!_FilterDatabase" and [Name] <> "'９月'!_FilterDatabase")),

    // 取得した各テーブルの Content 列を展開し
    // 必要な列「Employee」「Role」を抽出
    TblExpanded = Table.ExpandTableColumn(
        フィルターされた行1,
        "Content",
        {"日付", "明細", "金額", "サブ明細", "列1"}
    ),
    フィルターされた行 = Table.SelectRows(TblExpanded, each ([金額] <> null)),
    区切り記号の後に挿入されたテキスト = Table.AddColumn(フィルターされた行, "ラベル", each Text.AfterDelimiter([Name], "_"), type text),
    削除された列 = Table.RemoveColumns(区切り記号の後に挿入されたテキスト,{"Name"})
in
    削除された列

引当金と対応するサブ明細
let
    ソース = 集計対象の取得,
    フィルターされた行 = Table.SelectRows(ソース, each ([サブ明細] <> null) and ([明細] <> "引当金")),
    グループ化された行 = Table.Group(フィルターされた行, {"サブ明細","ラベル"}, {{"カウント", each List.Sum([金額]), type number}})
in
    グループ化された行

集計ベース
let
    ソース = 集計対象の取得,
    乗算済みの列 = Table.TransformColumns(ソース, {{"金額", each _ * -1, type number}}),
    削除された列 = Table.RemoveColumns(乗算済みの列,{"列1"}),
    削除された列1 = Table.RemoveColumns(削除された列,{"日付"}),
    結果 = Table.InsertRows(削除された列1,0,
        Table.TransformRows(引当金と対応するサブ明細,(row) =>[明細 = "引当金", サブ明細 = row[サブ明細], 金額 = row[カウント], ラベル = row[ラベル]])
    )
in
    結果
No results found