複数列の区切り文字で行分割しつつ、テーブルにする案｜Power Query

7281 ワード
PowerBI Excel PowerQuery Excel テキストリンク

区切り文字によるセル分割は列の分割メニューなら、簡単にできます。
ですが、そういうセルが複数列にわたる場合、列の分割操作では、上手くテーブルになりません。そこで、自分なりの解答を書き残しておきます。

※この問題はKen Pulsさんが出題されたものです。
Ken Pulsさんは「M Is for (Data) Monkey」の著者のお一人です。

設問

こんな具合で、左から右に変換したい。以下、区切り文字は改行文字だけである場合で解説します。

※下記コードを詳細エディタに貼ると、青い方のテーブルと同じものができます。このクエリを設問という名前にしてあるものとします。

設問
let
    Source = "i65W8swry89MTvV0UbIyNDI20VHyLEnNBfGUDA0MDWNKDQwMEg0NjKAsIwMjIyUdpcDSxLySzJJKkCqoBIQyBkoGFAHNA8pY6lmawvTDmeZAllKtDrq9pljtxbQNao0hVmtwmG2GYrYB3E8G2H1igmS2oQnccFPshpsjDAeZjWygIdwcsDGYei1Q9Bph12sG0hoLAA==",
    Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
    #"Imported JSON" = Table.FromRecords(Json.Document(Custom1,932))
in
    #"Imported JSON"

解答1～ハードコーディング

設問のテーブルと同じ列数、列名、順序でないと、動かないです。
でも、まずはここから始まるということでいいと思うんです。

コード

解答例1
let
    Source = 設問,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}, {"ItemID", type text}, {"Quantity", type text}, {"Price", type text}}),
    ColumnNames =Table.ColumnNames(Source),
    SplitByDelimiter = Table.TransformColumns(#"Changed Type",{
                    {"ItemID",each Splitter.SplitTextByDelimiter("#(lf)")(_)},
                    {"Quantity",each Splitter.SplitTextByDelimiter("#(lf)")(_)},
                    {"Price",each Splitter.SplitTextByDelimiter("#(lf)")(_)}
                    }
              ),
    EachRowToTable =Table.TransformRows(SplitByDelimiter,each
                        Table.FromColumns({{[InvoiceID]},[ItemID],[Quantity],[Price]},ColumnNames)                            
                    ),
    CombineTables = Table.Combine(EachRowToTable),
    #"Filled Down" = Table.FillDown(CombineTables,{"InvoiceID"})
in
    #"Filled Down"

処理の途中経過

SplitByDelimiterの箇所

区切り文字があるところもないところも、それぞれ、結果がリストになっています。

EachRowToTableの箇所

関数について

・Splitter.SplitTextByDelimiter関数
Splitter関数群のひとつで、1種の区切り文字を複数回使う場合に使用します。
他にも、複数の区切り文字を何回も使う場合はSplitter.SplitTextByAnyDelimiterが、
1回しか使わない場合はSplitter.SplitTextByEachDelimiterがあります。
・Table.TransformRows関数
操作感はTable.AddColumnに近いですね。
・Table.FromColumns関数
各列の内容をリストで表し、それをリストにまとめたものが引数になります。

解答2～データ部分の列名・列数の変動に対応

前提

  • 1列目は区切り文字による分割が不要であること。
  • 2列目以降に区切り文字による分割対象であること。
  • 2列目以降に入っている区切り文字の個数は、行で見た場合に各列で同じであること。

コード

関数にしてみました。

fx_複数列分割
(TBL as table,TitleColumn as text,delimiter as text)=>
let
    ColumnNames = Table.ColumnNames(TBL),
    NumOfColumns =Table.ColumnCount(TBL),
    //データ列をまとめて処理するための関数を用意する。各列名と相手方をペアにしたリストができる。
    fx_paring =(OtherParty)=>List.Zip({List.Skip(ColumnNames,1),
                                       List.Repeat({OtherParty},NumOfColumns-1)}
                             ),

    //テキスト型を保証しないと、Split関数が機能しないため。
    #"Changed Type" = Table.TransformColumnTypes( TBL,fx_paring(type text) ),

    Custom1 = Table.TransformColumns(#"Changed Type",
                    //Split関数をペアにしたリスト
                    fx_paring((x)=>Splitter.SplitTextByDelimiter(delimiter)(x))
              ),
    Custom2 = Table.Combine(
                    //テーブルの各行ごとに処理をし、その結果のリストが出来上がる。
                    Table.TransformRows(Custom1,each
                        //1行目は中身がリストでないので、リストに入れてやる。
                        Table.FromColumns({{Record.ToList(_){0}}} & List.Skip(Record.ToList(_),1),
                                          ColumnNames
                        )
                    )
              ),
    #"Filled Down" = Table.FillDown(Custom2,{TitleColumn})
in
    #"Filled Down"

実行例

こんな具合で詳細エディタの数式バーに入れてもらえれば、実行できます。

= fx_複数列分割(設問, "InvoiceID", "#(lf)")

実行例2

改行じゃなく、列が増えた例を設問2とし、それで実行してみます。

設問2
let
    Source = "jY+xCsIwEIbf5eZicmnSWjerSzZdXIxDkQ6FmoIkgojv7iWDto2Cyx33H3wf//EB2t6G7tzqLaxQ5DID7dpLuAA5omHIBU3BhYAM9r6xrnP38KXQsJzCQ9P7xnWDpXRtWG3YhtLdlaiUVItKBUpcJU14ZnOrSqypj2Q4k5GpTkw/FMVEwWMxnlaSaZ9xGZTRob47yo8jKMZsnGCt7/s3NDJT2HICE3/CisA6vQA=",
    Custom1 = Binary.Decompress(Binary.FromText(Source),Compression.Deflate),
    #"Imported JSON" = Table.FromRecords(Json.Document(Custom1,932))
in
    #"Imported JSON"
実行その2
= fx_複数列分割(設問2, "InvoiceID", "/")

このように、無事変換されています。

補足：fx_paring

ただのカスタム関数です。
例えば、type textを引数にして実行すると、こうなります。（分かりやすいよう、テーブルに変換しました）