+ Reply to Thread
Results 1 to 2 of 2

VB Code to Read Parameters into Power Query

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    VB Code to Read Parameters into Power Query

    For quite a while I've had a module to read parameters (and actually rewrite SQL) for MS-Query. It's great if you work with Oracle or some other database that uses SQL.

    PowerQuery doesn't use SQL so I needed another approach. Attached is the documentation, a module and a sample of workbook of how this is accomplished.

    Here is a copy / paste of the documentation to give you an idea of the process. The Summary at the end tells you the essential steps.

    Girl Scout Troop 704 is selling cookies. The troop’s sale is recorded on the Raw Data Sheet in a table called Table_Raw_Data

    I created the query by going to Data > From Table/Range and added in the following filters:
    • Boxes > 1
    • Date Between 1/1/2021 and 6/30/2021

    This created the sheet Table_Raw_Data with the table, Table_Raw_Data_2 and the query Table_Raw_Data.

    There are two modules in this workbook:
    • ModPQCode
    • ModTestCode

    The subroutines in ModPQCode are designed to have the parameters passed to them. The subroutines in ModTestCode pass the parameters to the subroutines in ModPQCode. This makes the modules more “plug and play” and you can run multiple queries in the same workbook without duplicating code.

    The subroutine for Get Code is intended to be used only once per query, immediately after creating the query with the hard-coded parameters.

    I did this for this query and then copied the original code to Cell A1 on the Original Code Sheet. This is not a necessary step. I did it only to preserve it for the sake of this lesson

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_Raw_Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Agent", type text}, {"Boxes", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Boxes] > 1),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] >= #datetime(2021, 1, 1, 0, 0, 0) and [Date] <= #datetime(2021, 6, 30, 0, 0, 0))
    in
    #"Filtered Rows1"

    This is what the code looked like on the Code Sheet before I started modifying it. I highlighted those parameters I wished to change. On the Code Sheet, I modified the code in column A to look like:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_Raw_Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Agent", type text}, {"Boxes", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Boxes] > @NumBox),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] >= #datetime(@StartDate) and [Date] <= #datetime(@EndDate))
    in
    #"Filtered Rows1"

    I used the “tokens” @NumBox, @StartDate and @EndDate as placeholders for the parameters I want to pass.

    In Column B I have the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@[Raw Code]],"@NumBox",Num_Box),"@StartDate",Start_Date),"@EndDate",End_Date)

    Please note that the SUBSTITUE command is case-sensitive.

    The SUBSTITUTE command replaces the tokens with values defined in the named ranges shown below. I use named ranges as a matter of convenience. If I move things around on the sheet where they are defined, I don’t have to change the VB code.

    End_Date ='Control Panel'!$C$4
    Num_Box ='Control Panel'!$B$2
    Start_Date ='Control Panel'!$C$3
    My_Code =Code!$B$1

    In this case, the query covers only one row of data. But all translated code is concatenated in cell B1 on the Code Sheet and this cell has the name My_Code.

    Then it is a matter of “filling in the blanks.”

    The number of boxes is in Cell B2 on the Control Panel. The beginning and end dates are in Cells B3 and B4. Different query languages prefer to used different date formats. For Power Query dates are handled as #datetime(yyyy, m, d, h, m, s) so I use a formula on the Control Panel sheet to format this parameter. These are shown in Cells C3 and C4.

    Fill in Cells B3 through B4 and click refresh data.

    Summary

    • Create the query with fixed parameters.
    • Tell GetCode where to find
    o The Code Sheet
    o Query Name
    o First cell in the Code Table
    • Run Get Code
    • Edit in tokens in the Code in Column A on the Code Sheet.
    • Use the SUBSTITUTE command in Column B to replace the tokens with the actual parameters.
    ---------- Do the above ONLY ONCE for each query ----------
    • Fill in the desired parameters in whatever cells you designate.
    • Press Refresh Data
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: VB Code to Read Parameters into Power Query

    Attachment did not add to the above - 7z files are not accepted.
    Attached Files Attached Files

+ 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. Replies: 0
    Last Post: 03-26-2021, 10:37 AM
  2. Power Query - Column Code Help
    By girltoni in forum Excel General
    Replies: 1
    Last Post: 10-07-2020, 02:24 PM
  3. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  4. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  5. Power Query, Excel 2013/ 2016, Dynamic URL, Set parameters
    By dude6571 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 08-17-2019, 12:29 PM
  6. [SOLVED] Creating Dynamic Parameters in Excel from link to Power Query
    By Stefj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2019, 06:58 AM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 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