[[20231005135541]]　『パワークエリ　パス』（ちょこ）　ページの最後に飛ぶ

『パワークエリ　パス』（ちょこ）

[20231003171943]
で相談してましたが
今日、再度数台のPCで試したらエラーが発生しました
常に無視以外でできるならやりたいです

 同時に使用できないプラ イバシー レベルの複数のデータ ソースにアクセスしています。
 このデータの組み合わせを再構築してください。

（やっていること）
システムから出力したデータを過年度で比較
1）フォルダ内の複数のデータを一括結合、データモデルへ
サンプルファイル

 let
    ソース = Folder.Files(パス & "TB"),
    ナビゲーション1 = ソース{0}[Content]
in
    ナビゲーション1」

2）キー一覧を作成（コードがあったりなかったりするので）、データモデルへ
テーブル1

 let
    ファイルパス = Excel.CurrentWorkbook(){[Name="パス"]}[Content]{0}[ファイルパス],
    ソース = Excel.Workbook(File.Contents(ファイルパス & "比較.xlsx"), null, true),
    #"A.コード（BS)_Sheet" = ソース{[Item="A.コード（BS)",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(#"A.コード（BS)_Sheet", [PromoteAllScalars=true]),
    追加されたカスタム = Table.AddColumn(昇格されたヘッダー数, "カスタム", each [コード]&"_"&[科目名]),
    並べ替えられた列 = Table.ReorderColumns(追加されたカスタム,{"??", "カスタム", "コード", "科目名"}),
    変更された型1 = Table.TransformColumnTypes(並べ替えられた列,{{"カスタム", type text}}),
    #"名前が変更された列 " = Table.RenameColumns(変更された型1,{{"カスタム", "コード_科目名"}}),
    並べ替えられた行 = Table.Sort(#"名前が変更された列 ",{{"??", Order.Ascending}})
in
    並べ替えられた行

3）1）の名称の重複削除、データモデルへ

テーブル2

 let
    ファイルパス = Excel.CurrentWorkbook(){[Name="パス"]}[Content]{0}[ファイルパス],
    ソース = Folder.Files(ファイルパス & "TB"),
    #"フィルター選択された非表示の 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(削除された他の列1{0}[ファイルの変換])),
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"コード", type text}}),
    #"名前が変更された列 " = Table.RenameColumns(変更された型,{{"Source.Name", "ファイル名"}}),
    区切り記号による列の分割 = Table.SplitColumn(#"名前が変更された列 ", "ファイル名", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"ファイル名.1", "ファイル名.2", "ファイル名.3"}),
    変更された型1 = Table.TransformColumnTypes(区切り記号による列の分割,{{"ファイル名.1", type text}, {"ファイル名.2", type text}, {"ファイル名.3", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型1, "カスタム", each [コード]&"_"&[科目名]),
    並べ替えられた列 = Table.ReorderColumns(追加されたカスタム,{"ファイル名.1", "ファイル名.2", "ファイル名.3", "カスタム", "コード", "科目名", "繰越残高", "借方", "貸方", "残高"}),
    変更された型2 = Table.TransformColumnTypes(並べ替えられた列,{{"カスタム", type text}}),
    #"名前が変更された列 2" = Table.RenameColumns(変更された型2,{{"カスタム", "コード_科目名"}}),
    変更された型3 = Table.TransformColumnTypes(#"名前が変更された列 2",{{"残高", Int64.Type}, {"貸方", Int64.Type}, {"借方", Int64.Type}, {"繰越残高", Int64.Type}}),
    削除された重複 = Table.Distinct(変更された型3, {"コード_科目名"})
in
    削除された重複
4）2）と3）を外部結合
5）コードと名称をキーに1）と2）をリレーション
6）ピポッドで表示

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

 おそらく、フォルダのパスをクエリで取得している以上、
 セキュリティの問題は解決できないと思うので、別（ちょっと乱暴）な方法をご提案

 PowerQueryエディターの[ホーム]タブの[パラメーター]グループにある[パラメーターの管理]→[新しいパラメーター]
 名前 FolderPath
 必須 チェック
 種類 テキスト
 提案されたテキスト
 現在の値 (適切な名前を設定してください。)

 とします。
 クエリでは、  
   ソース = Folder.Files(FolerPath & "\TB"),
 とするようにします。

 こうしておくと、パラメータを書き換えれば、変更が反映されるようになります(クエリの更新は必要です。)
 ただし、この方法だと、パラメータを書き換える時に毎回PowerQueryエディターを開かないといけないので、
 面倒というか、知らない人には敷居が高いというか。

 なので、パラメータの値を書き換えるVBAを使います。

 Sub sample()
    Dim path As String
    With Application.FileDialog(msoFileDialogFolderPicker)
       If .Show Then
          path = .SelectedItems(1)
          ThisWorkbook.Queries("FolderPath").Formula = """ " & path & """ meta [IsParameterQuery=true, Type=""Text"", IsParameterQueryRequired=true]"
          ThisWorkbook.Worksheets(1).Range("A1").Value = path
          ThisWorkbook.RefreshAll
       End If
    End With
 End Sub
 このマクロを実行すると、フォルダ選択ダイアログが開いて、フォルダを選択してOKすると
 パラメータFolderPathを書き換えて、クエリの更新を実施し、FolderPathの値を最初のシートのA1セルに書き出します。
 (このあたりは適当に変更してください。)
 WorkSheetChangeイベントを使えば、セルの値を書き換えたら、
 パラメータを書き換えてクエリ更新とかもできなくはないです
（´・ω・`） 2023/10/05(木) 16:48:09

今日はバタバタしているので
少し勉強して来週試してみます
質問いいですか

 >提案されたテキスト
 >現在の値 (適切な名前を設定してください。)

 提案されたテキストは1）～3）のどれを選択したらいいのでしょうか。
　1）任意の値
　2）値の一覧
　3）クエリ

 現在の値というのは
 現在のパスのことでしょうか
 それともテーブル名でしょうか
 パスはサーバー上で使ったり、個人PCで使ったりと毎回違いますが
 毎回ここの現在の値を書き換えるのでしょうか

（ちょこ） 2023/10/06(金) 09:10:17

 提案されたテキスト は　任意の値 です。書き漏らしました
 現在の値は、現在のパスです。

 >毎回ここの現在の値を書き換えるのでしょうか
 今回提示したコードは、変更される度に毎回実行する想定です。

 マクロでどこまで自動化するかは自由なので、
 ブックの保存されているフォルダから相対的なパスに自動的に設定するなど
 やろうと思えばなんでもできます
（´・ω・`） 2023/10/06(金) 12:47:57

（´・ω・`）さん
パスの書換が少なくてすむのが素敵です
ただ敷居が高いようで他の方法ができるとうれしいです

＞ブックの保存されているフォルダから相対的なパスに自動的に設定するなど

これはどういう意味なのでしょうか
一括結合の基データのあるフォルダにマクロでパスを表示してそれを
読むように設定するということでしょうか

何かできないか試行錯誤して行き詰ってしまいました

（ちょこ） 2023/10/11(水) 16:20:05

 解決すべき課題であるセキュリティ関連の話は、
 パスをパラメータに保存することで解決できていると思いますが、いかがでしょうか
 まずこれについて、返答が欲しいです。

 >ただ敷居が高いようで
 私には「敷居が高い」の意味がよく読み取れませんが、マクロを使いたくないということなら
 都度、PowerQueryエディタを開いて、手動で書き換えればいいと思います。

 パラメータをマクロで書き換えるのは付加的な（ある意味では余計な）便利機能なので
 使いたくないなら(マクロはマクロで、特有の面倒くささもあるので)使わない選択肢もあると思います

 そうではなく、マクロでもっと便利にしたいという話なら、
 どのようにしたいのか書いてください。 
（´・ω・`） 2023/10/11(水) 16:56:57

（´・ω・`）さん
セキュリティでパラメータで解決できました

マクロを使うのも大丈夫です（全然詳しくはないですが）
マクロ、セキュリティで引っ掛かりますが安全なファイルに設定できます

＞「敷居が高い」
現在のパスがファイルをどこで開くかで違うとおもいます
ここを書換るのはがクエリを知らない人には難しいという意味です
絵付きで使い方を作ってますが敷居が高いようです
私個人は十分使い勝手がよくなって満足してます

＞WorkSheetChangeイベントを使えば、セルの値を書き換えたら、
＞パラメータを書き換えてクエリ更新とかもできなくはないです

これがよくわからなかったです
Worksheet_Changeのことですか
よりよくなったほうがいいのでチェレンジしてますがまだできてません

（ちょこ） 2023/10/15(日) 10:13:41

 Worksheet_Changeです

 >よりよくなったほうがいいのでチェレンジしてますがまだできてません
 Worksheet_Changeにこだわる必要はないと思います

 どうなったら一番楽なんですか？それを書いてください。　
（´・ω・`） 2023/10/15(日) 10:53:18

クエリを知らない人でもできるよう
マクロの実行（ボタンクリック→フォルダ選択）やリボンのデータ＞データ更新をクリック
するだけでクエリの結果が出るようになればベストです

パスを手動で書換えるとかリレーションの設定とか、こういうのは
難しいようです

パスとセキュリティ問題がなければできてのですが
引続き何か良い方法を考えてみます

（ちょこ） 2023/10/15(日) 13:27:43

 具体的に説明するつもりが無いようなので、私はこれまでにします。
（´・ω・`） 2023/10/15(日) 17:16:20

すいません
具体的ということが分からないのですが
もう少し整理してご連絡します

（ちょこ） 2023/10/17(火) 15:44:45

 マクロ使うなら、こんな感じでも。
 （ソースに設定しているTBフォルダと比較.xlsxが、マクロブックと同じ階層にあることが条件）

 >     ファイルパス = Excel.CurrentWorkbook(){[Name="パス"]}[Content]{0}[ファイルパス],
 >     ソース = Folder.Files(ファイルパス & "TB"),

 >     ファイルパス = Excel.CurrentWorkbook(){[Name="パス"]}[Content]{0}[ファイルパス],
 >     ソース = Excel.Workbook(File.Contents(ファイルパス & "比較.xlsx"), null, true),

 ・これをやめて、クエリは最初に自動で記録されたものを、そのまま使用
 ・下記のマクロを、ThisWorkbookモジュールにコピペ
 ・これで、ソース指定部分は、毎回ブック起動時に自動で書き換えてくれる

 'https://stackoverflow.com/questions/36084508/how-to-change-source-of-a-power-query-using-vba
 Private Sub Workbook_Open()
    Dim pqTable As WorkbookQuery
    Dim oldSource As String
    Dim 旧フォルダ As String
    Dim 新フォルダ As String

    oldSource = Split(ThisWorkbook.Queries("サンプル ファイル").Formula, """")(1)
    旧フォルダ = Left(oldSource, InStrRev(oldSource, "\") - 1)
    新フォルダ = ThisWorkbook.Path

    For Each pqTable In ThisWorkbook.Queries
        pqTable.Formula = Replace(pqTable.Formula, 旧フォルダ, 新フォルダ)
    Next

 End Sub
（マナ） 2023/10/22(日) 18:57:35

コメント返信：

[ 一覧(最新更新順) ]

[[20231005135541]] 『パワークエリ パス』（ちょこ） ページの最後に飛ぶ

[[20231005135541]]　『パワークエリ　パス』（ちょこ）　ページの最後に飛ぶ