0
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PowerQuery Excelファイルの置いたパスで実行させたい時

Last updated at Posted at 2021-07-02

概要

Excelファイルを置いたPathを取得して、相対的にファイル一覧を取得してPower Queryを実行する為の方法。

やり方

  1. Excel 関数で、Power Queryに渡すためのデータを作っておく

    :Excel関数
    =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
    

image.png

  1. Power Queryで、上述のデータを利用して、フォルダ一覧取得(ファイル取得とかでも)

    フォルダ一覧取得
    = Folder.Contents(
      (Excel.CurrentWorkbook(){[Name="Settings"]}[Content]){0}[対象Path]
    )
    

image.png

理解促進の為の要素分解

問題解決の近道は分解ですね。

  1. = (Excel.CurrentWorkbook())

    image.png
  2. = (Excel.CurrentWorkbook(){0})
    = (Excel.CurrentWorkbook(){[Name="Settings"]})
    image.png
  3. = (Excel.CurrentWorkbook(){[Name="Settings"]}[Content])
    image.png
  4. = (Excel.CurrentWorkbook(){[Name="Settings"]}[Content]){0}
    image.png
  5. = (Excel.CurrentWorkbook(){[Name="Settings"]}[Content]){0}[対象Path]
    image.png
0
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?