Excel PowerQuery > ファイルパスを変数として設定する方法

PowerQueryを使いこなしていくと、数か月後にはPowerQueryのファイルパス（つまりソースとなるパス）を動的に操作する方法があれば、クエリはもっと良くなるのに、と思ったことがあるでしょう。

実は、あるんです！

この記事では、Excel Power Queryであたかも変数に値を導入して動的に値を変更する方法をご紹介します。

準備

まず、テーブル「Settings」を作成します。A列に「Name」B列に「Value」を入力します。A列には変数を、B列には値を入力します。参考までに今回のケースではCsv1Pathという変数を可変的にしていきたいと思います。

データ -> データの取得 -> クエリの結合 -> Power Queryエディターの起動をクリック

左のクエリエリアから右クリック=> 新しいクエリ => その他ソース => 空のクエリをクリックします。

詳細エディターをクリック

詳細エディターの中身には以下のように設定をします。詳細は次のコードをコピーして参照してみてください。

そしてクエリの名前をConfigに変更します。

let Config=(NameLabel) =>

let
    Source = Excel.CurrentWorkbook(){[Name="Settings"]},[Content],
    value = Source{[Name=NameLabel]}[Value]
in
    value

in Config

ファイルパスを動的に変化させる

ファイルを読み込んでいる部分を変更していたいと思います。

現状ではFile.Contains(“ファイルのフルパス”)になっているところを
File.Contains(Config(“ファイルの変数名”))に変更したいと思います。

Before
After

このように固定値であったファイルパスを変更することによって、変数のように扱うことができます。これを用いることによって、例えばExcelのひな形ファイルをテンプレートとして保存しておき、Settingsのテーブルの中身を変えるだけで様々なファイルを変更することができます。

163件のフィードバック

  4. Hey! Someone in my Facebook group shared this website with us so I came to take a look.
    I’m definitely loving the information. I’m bookmarking and will be tweeting this to my followers!
    Fantastic blog and outstanding design and style.

    Feel free to surf to my webpage – vpn special coupon

    返信

  82. Hi there, I found your site via Google while looking for a related topic, your web site came up, it looks good. I have bookmarked it in my google bookmarks.

    返信

  94. top 10 pharmacies in india [url=http://indianpharm24.pro/#]Best Indian pharmacy[/url] indian pharmacy paypal

    返信

  95. indian pharmacy [url=http://indianpharm24.pro/#]Pharmacies in India that ship to USA[/url] buy medicines online in india

    返信

  96. cheapest online pharmacy india [url=http://indianpharm24.pro/#]Indian pharmacy to USA[/url] india pharmacy mail order

    返信

  99. mail order pharmacy india [url=http://indianpharm24.pro/#]Indian pharmacy online[/url] online shopping pharmacy india

    返信

  102. Hi there! Do you know if they make any plugins to help with SEO?
    I’m trying to get my site to rank for some targeted keywords but
    I’m not seeing very good gains. If you know
    of any please share. Cheers! I saw similar
    text here: Eco product

    返信

  155. Petitfee&Koelf Black Pearl&Gold Hydrogel Eye Patch ⥇ For external use only. Avoid contact with eyes. Discontinue use if signs of irritation or rash appear. Keep out of reach of children. Use immediately after opening. The packaging and the product itself seems good quality. As soon as I put these patches under my eyes they felt really tingly and refreshing. Afterwards my eyes were less puffy and the dark circles looked better. I would definitely buy again. These cookies are used to deliver advertisements that are more relevant to you and your interests. Marketing Cookies are placed by third-party providers with our permission, and any information collected may be shared with other organizations such as publishers or advertisers. These cookies enable us to provide better services based on how users use our website, and allow us to improve our features to deliver better user experience. Information collected is aggregated and anonymous.
    https://lebanonhub.app/blogs/33544/permanent-eyebrows-for-blondes
    This is the most amazing mascara. The ingredients are safe and the performance is the best I’ve had. I wanted waterproof mascara and listened to the reviews. Even though this is not waterproof, there truly is no smudging!! I wear it all day and if I don’t wash my face before bed, I’m still smudge free in the morning. It doesn’t run if I cry either! It’s also milder on my eyes. They would water if they were irritated and sometimes felt heavy depending on the mascara I wore. This is light and has staying power. Okay, so I wish I didn’t know this information and I hope you never need it, but L’Oreal Double Extend Beauty Tubes Mascara is the best drugstore mascara to stand up to lots of crying. (Let’s just say last year was rough for me.) Save when you shop for the best mascara for sensitive eyes with these LookFantastic discount codes.

    返信

  157. Step 2. Use the brush to groom hairs upward and outward to blend color and set brows. Express Brow Ultra Slim Defining Eyebrow Pencil. The smooth formula glides across brows with no color streaks and no pencil breakage for precisely defined eyebrows that last! Pixi Natural Brow Duo ($16) Pair it with Maybelline New York Fashion Brow Color Drama Mascara to create the perfect brow. People with sparse brows might need a little more heft to fill in their brows, and that’s where a dual product like Brow Define + Fill Duo comes in. This has a lightly pigmented pencil on one end, and matching pigmented brow powder on the other end to achieve denser color. Use the two to custom-color in your brow as needed. For instance, some people need the powder to add more color in the thicker, front part of their brows, but for the thinner brow “tail” a more delicate pencil is needed for precision.
    https://cleangenmall.com/bbs/board.php?bo_table=free&wr_id=99528
    Anyone can use bronzer to boost your look, regardless how tan you may already be. Cardi B, Masai Martin, and countless others stand behind this bronzer. I get it: This stick formula, which moves from an easily blendable cream base to a dried-down powder, gives a very skin-like finish. It provides medium coverage and can work as a contour or bronzer depending on the shade you choose and the pressure you use. Unlike the usual drugstore cosmetics, a few beauty brands also produce bronzers that are not harmful to your skin, as they are made solely from organic substances. Much better for your skin and makeup! It’s impossible to think about bronzers without mentioning Benefit Cosmetics Hoola Matte Powder Bronzer. This silky-soft powder bronzer has a matte finish and is now available in four shades (Hoola, Hoola Lite, Hoola Toasted, and Hoola Caramel) to complement every skin tone. This cult classic is a winner of Allure’s Best of Beauty Awards and is perfect for a natural sun-kissed glow without the extra sparkle. It can instantly give you a little warmth like you were born with it. It’s super lightweight with customizable buildability. Not ready to invest in a full-size? It’s also available in a mini, and we guarantee you’ll fall in love with it.

    返信

  163. What¦s Taking place i am new to this, I stumbled upon this I have discovered It absolutely helpful and it has aided me out loads. I hope to give a contribution & assist different customers like its helped me. Good job.

    返信

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

目次