Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2021)
クエリのコピーについて
投稿日時: 25/01/19 15:00:15
投稿者: GOA

1月から12月のフォルダー12個の中にそれぞれ複数の施設のCSVデータが入っています。
SCVファイル名は「●●_20250101」「■■_20250101」となっていてそれぞれの月のフォルダーに「_20250201」「_20250301」と月ごとに振り分けて格納していきます。
これを集計するエクセルファイルのシート1に1月のフォルダー分、シート2に2月のフォルダー分といった具合に月ごとのフォルダーのデータを月のシートごとに集計したいのです。
1月分をクエリで取り込みデータクリーニングなどの処理を行ってひとまず完成しました。ここで行った処理を2月から12月分までそれぞれのシートごとで行いたいのです。一月ごとに処理を施していくのは大変だなと思い、ネット界隈でクエリのコピー方法をあさりましたが、別のエクセルファイルへのコピーだったので同じファイルの別シートにコピーする方法が見当たりませんでした。また同じフォルダーからのコピーなので求めている方法とは違うのかなと。
何か良い知恵がございましたらお力添えをお願いいたします。

回答
投稿日時: 25/01/19 15:32:33
投稿者: んなっと

例えば前回の質問
https://www.moug.net/faq/viewtopic.php?t=82966
であれば、
 
let
    MltExp = (Sor) =>
      let
        Sht = Sor{0}[Data],
        Plc = Text.AfterDelimiter(Sht{5}[Column2],":"),
        Skp = Table.Skip(Sht,9),
        Rem = Table.RemoveColumns(Skp,Table.ColumnNames(Skp){0}),
        Prm = Table.PromoteHeaders(Rem, [PromoteAllScalars=true]),
        Add = Table.AddColumn(Prm, "場所", each Plc)
   in
        Add,
    Src = Folder.Files("E:\office\Excel\ExcelVBA\施設売上"),
    Add = Table.AddColumn(Src,"Tbl", each MltExp(Excel.Workbook([Content]))),
    Del = Table.SelectColumns(Add,{"Tbl"}),
    Exp = Table.ExpandTableColumn(Del, "Tbl", Table.ColumnNames(Del[Tbl]{0}), Table.ColumnNames(Del[Tbl]{0}))
in
    Exp
 
のクエリを【複製」して詳細エディターを開き、
"E:\office\Excel\ExcelVBA\施設売上"の部分を別のフォルダー名に書き換えるだけです。
 
今回は状況が少し異なりますが、参考にして試してみてください。

投稿日時: 25/01/19 21:08:55
投稿者: GOA

複製→詳細エディターで教えていただいた方法でやってみたところうまくいきました。
が、実は施設を示すセルやヘッダーにしたい行などが微妙に違う似たような別のデータも整理する必要があり、「サンプルファイルの変換」からカスタム列を追加したりしたものがあるのです。
詳細エディターを見るとサンプルファイルのファイル名がいくつかの個所にありました。違う月のフォルダーには●●_20250201と違うファイル名で格納されているので「見つかりません」とエラーになります。

回答
投稿日時: 25/01/20 09:20:08
投稿者: んなっと

下の\111を統合して、クエリの複製で\222も統合する場合を書きます。
どちらのフォルダーにも同じ形式のcsvファイルしか存在せず、別の余計なファイルがないことが前提です。
 
E:\office\Excel\ExcelVBA−\111−1111.csv
            |  └2222.csv
            └\222−3333.csv
               └4444.csv
 
《最初の手順》
 データの取得→ファイルから→フォルダーから
→\111を選んで 開く
→結合→データの結合と変換
→サンプルファイル:最初のファイル→OK
→サンプルファイルの変換→
→余計な行や列を削除、1行目をヘッダーとして使用 などの操作
 
いくつかのクエリを選択して、詳細エディターを開いてみましょう。
 
【ヘルパークエリ】
 
●サンプルファイル →ここには当然\111が記述されます。
 
let
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\111"),
    ナビゲーション1 = ソース{0}[Content]
in
    ナビゲーション1
 
●サンプルファイルの変換 →ここには\111とか1111.csvといった特定のフォルダーやファイル名は存在してはいけません。
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    削除された列 = Table.RemoveColumns(ソース,{"Column1"}),
    削除された最初の行 = Table.Skip(削除された列,9),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された最初の行, [PromoteAllScalars=true])
in
    昇格されたヘッダー数
 
【その他のクエリ】
 
●111 →ここにも当然\111が記述されますが、1111.csvといった特定のファイル名は存在してはいけません。
 
let
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\111"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    削除された列 = Table.RemoveColumns(展開されたテーブル列1,{"Source.Name"})
in
    削除された列
 
 
《いよいよ複製です》
 ●111で右クリック→複製→詳細エディターでフォルダー名を変更・クエリの名前も変更
 
●222
 
let
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\222"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    削除された列 = Table.RemoveColumns(展開されたテーブル列1,{"Source.Name"})
in
    削除された列
 
以上。どうですか?
《いよいよ複製です》以降の手順では ●サンプルファイル や ●サンプルファイルの変換 は手を加えていません。

投稿日時: 25/01/20 10:32:29
投稿者: GOA

いつもありがとうございます。
→余計な行や列を削除、1行目をヘッダーとして使用 などの操作
は全てサンプルファイルの変換で行わなければならないということでしょうか?
 
行っている処理は
【サンプルファイルの変換】で
○4行削除
○列の追加で駐車場名の列を追加する
=Text.Replace(ソース{0}[Column1], "駐車場名:", "")
 
【その他のクエリ】で
○1行目をヘッダーとして使用
○追加した列のヘッダー名を変更
○追加した列を先頭に移動
○不要な列の削除
○清算時刻の列で「エラーの削除」
○清算時刻の列で空白を削除
 
サンプルファイルの変換で全ての処理を行うと
○1行目をヘッダーとして使用
○不要な列の削除(Column1の列が含まれている)
を操作したときに
「テーブルの列 'Column1' が見つかりませんでした。」とエラーになってしまうのでその他のクエリで処理をしていました。
 
このエラーの解決が先ということでしょうか?
 
 
 
 

回答
投稿日時: 25/01/20 23:35:15
投稿者: んなっと

サンプルファイルの変換で
○1行目をヘッダーとして使用
○不要な列の削除(Column1の列が含まれている)
 
       ↓
サンプルファイルの変換で
○不要な列の削除(Column1の列が含まれている)
○1行目をヘッダーとして使用

投稿日時: 25/01/21 00:55:59
投稿者: GOA

「サンプル ファイルの変換」で
不要な列の削除をしたら
「その他のクエリ」のファイル名の頭に△びっくりマークが表示されてエラーになってしまいます。
「テーブルの列 'Column1' が見つかりませんでした」
 
【サンプル ファイルの変換の詳細】
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    削除された最初の行 = Table.Skip(ソース,4),
    追加されたカスタム = Table.AddColumn(削除された最初の行, "カスタム", each Text.Replace(ソース{0}[Column1], "駐車場名:", "")),
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"Column1"})
in
    削除された列
 
 
【その他のクエリの詳細】
let
    ソース = Folder.Files("C:\Users\anjun\OneDrive\デスクトップ\1月日本信号1"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}})
in
    変更された型

回答
投稿日時: 25/01/21 05:48:00
投稿者: んなっと

1111.csv が次のような構成の場合。
 
           A   B   C  D
 1         aaa        
 2                  
 3                  
 4                  
 5 駐車場名:アアアア        
 6                  
 7                  
 8                  
 9                  
10           名前 分類 値
11            あ   P 10
12            い   q 11
13            う   r 12
14            え   P 13
15            お   q 14
16            か   r 15
17            き   P 16
18            く   q 17
19            け   r 18
 
 
●サンプルファイルの変換
 行削除、列削除、ヘッダーに昇格などの処理
→詳細エディターを開いてソースのすぐ次に赤字の行を追加し、
 A5セルの駐車場名の情報をPlcに格納する
→最後に駐車場の列とPlcを追加する
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{4}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,9),
    削除された列 = Table.RemoveColumns(削除された最初の行,{"Column1"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された列, [PromoteAllScalars=true]),
    追加されたカスタム = Table.AddColumn(昇格されたヘッダー数, "駐車場", each Plc)
in
    追加されたカスタム
 
名前 分類 値 駐車場名
 あ   P 10 アアアア
 い   q 11 アアアア
 う   r 12 アアアア
 え   P 13 アアアア
 お   q 14 アアアア
 か   r 15 アアアア
 き   P 16 アアアア
 く   q 17 アアアア
 け   r 18 アアアア

回答
投稿日時: 25/01/21 05:52:12
投稿者: んなっと

考え方は前回の質問に対する回答とほとんど同じです。
 
let
    MltExp = (Sor) =>
      let
        Sht = Sor{0}[Data],
        Plc = Text.AfterDelimiter(Sht{5}[Column2],":"),
        Skp = Table.Skip(Sht,9),
        Rem = Table.RemoveColumns(Skp,Table.ColumnNames(Skp){0}),
        Prm = Table.PromoteHeaders(Rem, [PromoteAllScalars=true]),
        Add = Table.AddColumn(Prm, "場所", each Plc)
   in
        Add,
    Src = Folder.Files("E:\office\Excel\ExcelVBA\施設売上"),
    Add = Table.AddColumn(Src,"Tbl", each MltExp(Excel.Workbook([Content]))),
    Del = Table.SelectColumns(Add,{"Tbl"}),
    Exp = Table.ExpandTableColumn(Del, "Tbl", Table.ColumnNames(Del[Tbl]{0}), Table.ColumnNames(Del[Tbl]{0}))
in
    Exp
 
回答を得たら、自分でそれを応用できるようになってください。

投稿日時: 25/01/23 01:10:19
投稿者: GOA

CSVの構成はこのようになっています。
           A   B  C  ・・・・・
 1 駐車場名:アアアア        
 2                  
 3 明細記録                  
 4                  
 5         区分 分類 値  ・・・・・       
 6          あ  a 2                  
 7  い  b  6               
 8    う  c 9               
  
サンプルファイルの変換で行の削除→カスタム列の追加→カスタム列を先頭に移動→不要な列を削除→1行目とヘッダーとして使用→エラーの削除→空白をフィルター
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{4}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
    追加されたカスタム = Table.AddColumn(削除された最初の行, "カスタム", each Text.Replace(ソース{0}[Column1], "駐車場名,each Plc )),
    並べ替えられた列 = Table.ReorderColumns(追加されたカスタム,{"カスタム", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    削除された列 = Table.RemoveColumns(並べ替えられた列,{"Column1", "Column2", "Column5", "Column8", "Column9", "Column10", "Column12", "Column15"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された列, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"ザ・パーク上福岡町", type text}, {"入車時刻", type datetime}, {"精算時刻", type datetime}, {"料金", Int64.Type}, {"現金", Int64.Type}, {"預り金", Int64.Type}, {"未払金", Int64.Type}, {"電子マネー", Int64.Type}}),
    削除されたエラー = Table.RemoveRowsWithErrors(変更された型, {"精算時刻"}),
    フィルターされた行 = Table.SelectRows(削除されたエラー, each ([精算時刻] <> null))
in
    フィルターされた行
としたところ
トークン ',' が必要です。となってしまいました。何かが足りないのでしょうか?
度々でもうしわけないです。

回答
投稿日時: 25/01/23 06:54:18
投稿者: んなっと

    Plc = Text.AfterDelimiter(ソース{0}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
    追加されたカスタム = Table.AddColumn(削除された最初の行, "カスタム", each Plc),

投稿日時: 25/01/24 09:43:10
投稿者: GOA

let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{0}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
    追加されたカスタム = Table.AddColumn(削除された最初の行, "カスタム", each Plc),
 
↑この処理まではエラーが出ませんが↓以降の処理(列の削除や1行目をヘッダー)で「テーブルの列 'Column1' が見つかりませんでした。」のエラーがでます。
 
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"Column1", "Column2", "Column5", "Column8", "Column9", "Column10", "Column12", "Column15"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された列, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"入車時刻", type datetime}, {"精算時刻", type datetime}, {"料金", Int64.Type}, {"現金", Int64.Type}, {"預り金", Int64.Type}, {"未払金", Int64.Type}, {"電子マネー", Int64.Type}, {"ザ・パーク上福岡町", type text}})
in
    変更された型
 
「サンプルファイルの変換」で列の削除や1行目ヘッダーの処理を行ってはいけないということでしょうか?
 

回答
投稿日時: 25/01/24 11:38:59
投稿者: んなっと

最後に駐車場の列とPlcを追加する と書きましたよね?なぜ守らないのですか?
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{0}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
    削除された他の列 = Table.SelectColumns(削除された最初の行,{"Column3", "Column4", "Column6", "Column7", "Column11", "Column13", "Column14"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された他の列, [PromoteAllScalars=true]),
    追加されたカスタム = Table.AddColumn(昇格されたヘッダー数, "駐車場", each Plc)
in
    追加されたカスタム

投稿日時: 25/01/25 09:12:06
投稿者: GOA

「最後に」を読み落としていました。すみません。
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{0}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
 
↑この処理まではエラーが出ませんが↓以降の処理(列の削除や1行目をヘッダー)で「テーブルの列 'Column1' が見つかりませんでした。」のエラーがでます。
 
    削除された列 = Table.RemoveColumns(削除された最初の行,{"Column1", "Column2", "Column5", "Column8", "Column9", "Column10", "Column12", "Column15"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された列, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"入車時刻", type datetime}, {"精算時刻", type datetime}, {"料金", Int64.Type}, {"現金", Int64.Type}, {"預り金", Int64.Type}, {"未払金", Int64.Type}, {"電子マネー", Int64.Type}}),
    追加されたカスタム = Table.AddColumn(変更された型, "カスタム", each Plc)
in
    追加されたカスタム
 
「サンプルファイルの変換」で列の削除や1行目ヘッダーの処理を行うとエラーがでます。

回答
投稿日時: 25/01/25 12:08:11
投稿者: んなっと

ようやくゴールが見えてきました...
 
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=15, Encoding=932, QuoteStyle=QuoteStyle.None]),
    Plc = Text.AfterDelimiter(ソース{0}[Column1],":"),
    削除された最初の行 = Table.Skip(ソース,4),
    削除された他の列 = Table.SelectColumns(削除された最初の行,{"Column3", "Column4", "Column6", "Column7", "Column11", "Column13", "Column14"}),
    昇格されたヘッダー数 = Table.PromoteHeaders(削除された他の列, [PromoteAllScalars=true]),
    追加されたカスタム = Table.AddColumn(昇格されたヘッダー数, "カスタム", each Plc)
in
    追加されたカスタム
 
 
【サンプルファイルの変換】のエラーが消えましたね。
→次に【その他のクエリ】の
 変更された型 = ... {"Column1", ...
のステップは、すぐ左の×をクリックして削除しましょう。
以前の間違えた手順が原因で"Column1"が残ってしまっています。こんな感じになると思います。
  
let
    ソース = Folder.Files("C:\Users\anjun\OneDrive\デスクトップ\1月日本信号1"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列 = Table.SelectColumns(#"名前が変更された列 1",{"ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル")))
in
    展開されたテーブル列1
  
 
→最後に詳細エディターを閉じて Ctrl+A ですべての列を選択→ 変換→データ型の検出
 これで 変更された型 = のステップがエラーなしで追加されます。
  
let
    ソース = Folder.Files("C:\Users\anjun\OneDrive\デスクトップ\1月日本信号1"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列 = Table.SelectColumns(#"名前が変更された列 1",{"ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"精算時刻", type datetime}, {"料金", Int64.Type}, {"現金", Int64.Type}, {"預り金", Int64.Type}, {"未払金", Int64.Type}, {"電子マネー", Int64.Type}, {"入車時刻", type datetime}, {"カスタム", type text}})
in
    変更された型

回答
投稿日時: 25/01/25 12:46:58
投稿者: んなっと

PowerQueryエディターで
 ファイル
→オプションと設定
→クエリのオプション
→データの読み込み
→非構造化ソースの列の方とヘッダーを検出しない にチェック
 
としておけば、今後は 変更された型 = のステップが自動追加されることはありません。
今回のようなケースでは、邪魔。

投稿日時: 25/01/25 13:26:37
投稿者: GOA

できました!!複製も問題なく作成できました。
私の理解が浅くお腹立ちの事もあったかと思いますが、辛抱強くお付き合いいただき大変感謝いたします。
あるがとうございました!!!