+ Reply to Thread
Results 1 to 4 of 4

Power Query - replace file name and sheet with variable

Hybrid View

  1. #1
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,872

    Power Query - replace file name and sheet with variable

    I know Excel formulas very well, and am pretty good at VBA, but I don't know Power Query really at all. I get information from a file on our OneDrive for Business server through Power Query.
    Here is the M Code:

    let
        Source = Excel.Workbook(Web.Contents("https://zraNotReal.sharepoint.com/sites/Test/Shared%20Documents/General/Huddles/TAM%20Huddle%test.xlsm"), null, true),
        #"February 2023_Sheet" = Source{[Item="February 2023",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(#"February 2023_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type number}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}})
    in
        #"Changed Type"

    What I would like to do is replace the file name (TAM%20Huddle%test.xlsm, OR the whole string starting with https://...) and sheet name with a variable that I could set in my workbook.
    For instance, I could name cell A1 as "MyFileName" and cell A2 as "MySheet" and have the name of the file and sheet respectively in those cells. In essence, what I want is something like this:

    let
        Source = Excel.Workbook(Web.Contents(MyFileName), null, true),
        #"February 2023_Sheet" = Source{[Item=MySheet,Kind="Sheet"]}[Data]...
    But don't know the proper syntax

    Moderator Comment: I have updated your post to include Code tags. Please employ them in the future.
    Last edited by alansidman; 04-08-2023 at 12:00 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,981

    Re: Power Query - replace file name and sheet with variable

    Create a parameter query with the variable.

    This link should help you to develop it. https://www.myonlinetraininghub.com/...riables-3-ways
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,872

    Re: Power Query - replace file name and sheet with variable

    When researching before posting, I saw that post, but I didn't think it addressed my specific issue, mostly because I have no knowledge of Mcode. I took another look, and after some good guessing and going slow, I finally got it to work!! Thank you!

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,981

    Re: Power Query - replace file name and sheet with variable

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power Query - Feeding dynamic sheet name to Power query
    By egoosen3 in forum Excel General
    Replies: 5
    Last Post: 09-13-2023, 07:41 AM
  2. Replace formula using power query
    By Jclarka in forum Excel General
    Replies: 1
    Last Post: 09-12-2022, 06:42 PM
  3. [SOLVED] Power Query: how to use a formula to replace values
    By paulma1960 in forum Excel General
    Replies: 4
    Last Post: 01-27-2022, 11:09 AM
  4. [SOLVED] Append and replace in Power Query
    By punter in forum Excel General
    Replies: 3
    Last Post: 04-08-2020, 01:25 AM
  5. Power Query to replace macros
    By lynnsong986 in forum Excel General
    Replies: 9
    Last Post: 11-12-2019, 10:39 AM
  6. Power Query - Replace If Text is Found
    By brent_milne in forum Excel General
    Replies: 1
    Last Post: 10-25-2018, 11:00 AM
  7. [SOLVED] Power Query - One works the other does not (replace with if statement) - Why?
    By learning new things in forum Excel General
    Replies: 13
    Last Post: 08-20-2018, 09:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1