[[20220930142746]]　『PowerQuery　クエリのプロパティーの名前を取り込』（なるへそ）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『PowerQuery　クエリのプロパティーの名前を取り込む』（なるへそ）

いつもお世話になります

PowerQueryのプロパティーの名前を取り込みたいのですが何か方法は有るのでしょうか
エディタ―でタブをポチポチしたが分からず、過去検索してもそれらしいものがわからず
ネットで検索するとなぜか結合っぽいものが表示されて行きつきませんので教えてください

現在はPowerQueryで取り込んでおらず閉じて読み込んだテーブルの範囲外に下記式でシート名を読み込んで
取り込んだテーブルの最終行以上の2000行まで余裕みてコピーしています
=IF($AF2<>"",RIGHT(LEFT(CELL("filename",A2),FIND(".",CELL("filename",A2))-1),6),"")
右から6文字しか読んでないのは6文字が9月なら202209になるからです

毎月1000から2000行(可変)ほどコピーするのもそれほど手間ではないのですが
前月のファイルを開いて当月にデータソースを変更して作成しておりステップの中でフィルターをかけて行を削除しているので
セルの番地が中抜けしてしまい、また下の方は消えてしまい、毎回コピーしています

O365なのでPowerQueryでなく2行目から関数で必ず2000行までスピル出来るのでもいいです

文字ばかりでわかりにくかったら申し訳ないですが宜しくお願いします

＜ 使用 Excel：Office365、使用 OS：Windows10 ＞ 

 ワークシート関数ではたぶん取得できないと思います。
 WorkbookQueryのName,Descriptionプロパティなどを取得しては？
 以下、考え方のサンプルです。（イミディエイトウインドウに出力します）
 Sub test()
     Dim qr As WorkbookQuery
     For Each qr In ActiveWorkbook.Queries
         Debug.Print qr.Name; "  "; qr.Description
     Next
 End Sub
 　
（γ） 2022/09/30(金) 15:24
γさん返信ありがとうございます
PowerQueryでは直接取り込めず、マクロでと言う事になるのですね

直接誰かに渡すファイルではなくCSVを読み込んで体裁を整えるのにPowerQuery使っているだけで
最終的は提出用フォーマットにテキストでコピペしてるだけなのです
01などだとExcelが勝手に1に書き換えるので01に置換したり、列挿入したり、入れ替えたり

マクロはほぼ書けない(PowerQueryも適当ですが・・・)のですがネット検索しながらやってみます
（なるへそ） 2022/09/30(金) 15:50 

 私は、使用したクエリーの名前のつもりですが、
 01が１になってしまうとか、何か関係があるんですか？


 何か話がかみあっていないような気がしてきました。
 あなたのおっしゃる「クエリーのプロパティ」というのを
 もう少し詳しく説明して下さい。
 皆さんからコメントがつくはずです。
 　
（γ） 2022/09/30(金) 16:01
ソースのcsvのファイル名ならクエリで取得できるのですが
それではだめ？

（マナ） 2022/09/30(金) 16:03

γさん
すみません。PowerQueryもはっきりわかっておらず(詳細エディタの書き方もあまりわかってません)
01が1になってしまうのは今回の内容には全く関係ございません。ややこしいことを書いてすみません

PowerQueryのエディタを開くとクエリの設定枠の適用したステップ欄の上にプロパティがあり
名前の枠内に記載されている文字をPowerQueryで取り込みたかったのです
ファイルを取り込むとファイルのファイル名なのかシート名なのかが表示されます

今回の内容はそれを取り込みたいだけでその他のステップは関係ないのですが
最初の質問に書いた様に現在のやり方で行くと適当なところまでフィルコピーするだけで
手間はそんなにかからず1から2操作くらいで終わってしまいます
マクロで考えるならその他のステップで行っていることもPowerQueryでなくマクロを使って
1操作程度で終わらせるように考えようかなと思っただけです
（なるへそ） 2022/09/30(金) 16:21

マナさん　ご返信ありがとうございます
そうですねソースの.CSVの前はその名前になっているのでどこから読み込めますでしょうか
あとは置換して要らない文字を消しますので
宜しくお願いします
（なるへそ） 2022/09/30(金) 16:24 
 回答ありがとうございます。指しているものはおなじもののようです。
 ただ、私の案ですと、そのシートに読み込んだクエリーと同定することができないようなので、
 没ですね。


 マナさんの指摘にあるように、読み込みする際に、
 CSVのファイル名そのものを一つの列に書き込むこともできます。
 たぶん、それがベストの案でしょう。


 クエリー名自体がワークシート名になるので、今のものでもよいかとも
 思いますが、マナさんのご指摘に沿って考えられることをお薦めします。それでは。
 　
（γ） 2022/09/30(金) 16:36

 >前月のファイルを開いて当月にデータソースを変更して作成しており
 >ステップの中でフィルターをかけて行を削除しているので
 >セルの番地が中抜けしてしまい、また下の方は消えてしまい、毎回コピーしています


 ホントの課題はこっちですよね？


 クエリ名を取得したいというのは、ご自分で考えた解決策だと思いますが、
 根本を解決する方法が多分あります


 最初から相談してみてはいかがでしょうか
（´・ω・`） 2022/09/30(金) 16:49
γさん　再度のご連絡ありがとうございます。前回の回答の中身がわかっておらず申し訳ないです
今マナさんが教えてくれた方法をでソースに表示しているものを取り込もうと
検索しているのですが検索が下手なのかなかなかこれといったHPが見つからず四苦八苦しています
ちょっと今からは時間が取れなくなりますし、今月分の処理は一応終わったので月曜日以降に
また考えます(来月用に＾＾；）
（なるへそ） 2022/09/30(金) 16:59
こんな感じです。
＞当月にデータソースを変更
この作業は必要なくなります。

１）作業用フォルダを用意
２）CSVを格納
３）データの取得/ファイルから/フォルダから　で１）のフォルダを指定
４）データの変換
５）[Content]と[Name]列以外を削除
６）[Name]列から、年月数字6桁を抜き出し
７）[Content]列を展開
８）以降の操作は、現在のクエリと同じ

翌月からは、フォルダ内のcsvを差し替えて、クエリ更新するだけ

（マナ） 2022/09/30(金) 17:05

違っていたらごめんなさい。
たぶん、こういうこと？

let 

    ExceptSection1 = Record.ToTable(
        Record.Combine(
            {
                Record.RemoveFields(
                    #shared,
                    Record.FieldNames(#sections[Section1])
                ),
                [
                    #"#table" = #table,
                    #"#date" = #date,
                    #"#time" = #time,
                    #"#datetime" = #datetime,
                    #"#datetimezone" = #datetimezone,
                    #"#duration" = #duration,
                    #"#binary" = #binary
                ]
            }
        )
    ),
    SelectType =
        Table.SelectRows(
            ExceptSection1,
            each
                Type.Is(
                    Value.Type( [Value] ),
                    Function.Type  // Type.Type にすれば 定義された Typeの一覧に
                )
        ),
    AddedDescription = 
        Table.AddColumn(
            SelectType,
            "Description",
            each
                Value.Metadata(
                    Value.Type([Value])
                )[Documentation.Description]?,
            type text),
    SortedRows = Table.Sort(
            AddedDescription,
            { "Name", Order.Ascending }
        )
in
    SortedRows
（まっち） 2022/09/30(金) 17:06
（´・ω・`） さん　ご連絡ありがとうございます
ぶっちゃけはそうですね

最初の質問の最後に書いた様にスピルでもなんでも行を削除してもずれたり消えないものが
書ければよいのですがPowerQueryで取り込めれば最終行以上とか考えなくて良いかなと・・・

最初はINDAIRECTとROWで書いたら行はずれないかなと思ったのですが
行削除したら結局下の方が消えるなと

最悪は2回別ファイルにコピペするとかかな
それをマクロにするかフィルコピーの分とコピペ一回分マクロにする方が
全部考えるより早いですね

また、別途質問方法も内容も考えます。ありがとうございます
（なるへそ） 2022/09/30(金) 17:18

マナさん　まっちさん　ご連絡ありがとうございます
確認は来週以降になります。ごめんなさい。

（なるへそ） 2022/09/30(金) 17:32

 フィルタの条件をファイル名とか日付から生成できれば解決に一歩ちかっくんですか？
 そのあたりも含めて相談されるといいと思います
（´・ω・`） 2022/09/30(金) 18:29
＞セルの番地が中抜けしてしまい、また下の方は消えてしまい、毎回コピーしています 

 =IF(INDIRECT("AF2:AF2000")<>"",RIGHT(TEXTAFTER(CELL("filename",A1),"]",-1),6),"")

（マナ） 2022/09/30(金) 18:43

こうか 

 =IF(INDIRECT("A2:A2000")<>"",RIGHT(CELL("filename",A1),6),"")

（マナ） 2022/09/30(金) 18:46

γさん、マナさん、まっちさん、´・ω・`さん色々とご提案ありがとうございました
マナさんのPowerQueryでの処理は 6)までは出来たのですが 7)の展開からうまくいかず
展開の方法がわかりません。バイナリの所をクリックするとファイルは読み込むのですが
読み込んだデータに202209が表示される箇所がありませんので私の操作は何か間違っている気がします
Content、インポートされたCSV、昇格されたヘッダー数、変更された型が表示されてファイルを読み込んでます。下記の様になります 
 削除された他の列 = Table.SelectColumns(ソース,{"Content", "Name"}),
    抽出されるテキスト範囲 = Table.TransformColumns(削除された他の列, {{"Name", each Text.Middle(_, 6, 6), type text}}),
    Content = 抽出されるテキスト範囲{0}[Content],
    #"インポートされた CSV" = Csv.Document(Content,[Delimiter=",", Columns=18, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(#"インポートされた CSV", [PromoteAllScalars=true]),


 変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,・・・・
in
    変更された型
です

まっちさんの関数？も650行ほど確認しましたが使用方法がわからず、申し訳ございません

´・ω・`さんへ日本語は文章だけだとなかなか伝わりにくいことも多いので色々とまた考えてみます

最終は最後にマナさんが紹介してくれましたスピルする方法を頂き
=IF(INDIRECT("A1:A2000)<>"",RIGHT(LEFT(CELL("filename",A1),FIND(".",CELL("filename",A1))-1),6),"")
としました
マナさんの関数をそのまま記入するとファイル名とシート名が同じ時に変になったので（一番後ろから6文字で9.xlsxになった）

これで想定通りになりひと手間省けました。みなさんありがとうございました

（なるへそ） 2022/10/04(火) 12:45

＞7)の展開からうまくいかず

申し訳ありません。試さずに回答しました。
こんな手順でした。 

 7-1）[Content]列でファイルの結合（見出し右端ボタン）
 7-2）適用したステップ欄で、下から３つめ「削除された他の列1」を右クリックで、「最後まで削除」
 7-3）[Content]列を削除
 7-4）[ファイルの変換]列で展開（見出し右端ボタン）

（マナ） 2022/10/04(火) 16:20

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.