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のテーブルの中身を変えるだけで様々なファイルを変更することができます。

393件のフィードバック

  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.

    返信

  170. Hello! I know this is kinda off topic but I was wondering if you knew where I could geta captcha plugin for my comment form? I’m using the sameblog platform as yours and I’m having problems finding one?Thanks a lot!

    返信

  179. I enjoy what you guys are up too. This type of clever work and reporting!Keep up the fantastic works guys I’ve incorporated you guys to blogroll.

    返信

  198. At this time it seems like WordPress is the preferred blogging platform out there right now. (from what I’ve read) Is that what you are using on your blog?

    返信

  217. Hello! I’ve been reading your blog for a long time now andfinally got the courage to go ahead and give you ashout out from Houston Tx! Just wanted to say keep up the fantastic job!

    返信

  241. “It will be an honour to lead the team at both St. Andrew’s and on the road in the league, along with the cup competitions in which Blues have a tradition of success.

    返信

  250. I am really impressed with your writing skills and also with the layout on your blog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it’s rare to see a nice blog like this one these days..

    返信

  314. That consolidation set up Bitcoin for a strong run in 2013 when it began to attract more notice outside a niche audience of techies and hobbyists. The world’s first Bitcoin ATM was installed in Vancouver, allowing buyers to change fiat money for crypto. By the end of January, Bitcoin’s price had already risen to more than $20. The momentum built from there, as interest spread. by Jason Nelson, Andrew Hayward · 2 min read “Too often in the past the price crash was timed so that ordinary investors buying Bitcoin during the bubble are the ones who lose out,” she told the BBC. Bitcoin’s price is following a cyclical pattern, going through peaks and troughs, which are correlated with its halvings. With every halving, Bitcoin’s supply is cut in half, making it more scarce. Such halvings happen every 4 years approximately. Initially, 50 BTC were added into circulation with each block. Having seen three havlings already, Bitcoin’s supply issuance currently stands at 6.25 BTC every 10 minutes on average. The next halving will take place in 2024, cutting the amount per block to 3.125 BTC. Miners are projected to mine the last BTC around 2140.
    http://cosquadebboi1972.iamarrows.com/https-changehero-io-buy-btc
    Thank you for submitting your question.Keep reading Forbes Advisor for the chance to see the answer to your question in one of our upcoming stories. Our editors also may be in touch with follow-up questions. You can earn up to $100 in crypto as a reward for every eligible referral. Yes and no. Crypto is a secure method to buy crypto and actively trade it, but an external hardware wallet is a more secure storage option if you plan to store large amounts of crypto for a long period of time. Crypto did not immediately respond to USA TODAY for comment. It’s your do-it-all digital wallet. Load up on cash back offers before you shop. Track your packages. And manage it all on the go. Zengo is a must-have crypto wallet for those seeking a secure non-custodial wallet on which to store cryptocurrencies and NFTs as well as access DeFi trading and staking platforms.

    返信

  360. Hello just wanted to give you a quick heads up and let you know a few of the pictures aren’t loading properly.I’m not sure why but I think its a linking issue.I’ve tried it in two different browsers and both show the same results.

    返信

コメントを残す

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

目次