Category: Life

rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

Posted on by Bb

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  Here’s the very quick and easy way to do this!

This exact solution works in Excel. Creating a dynamic SQL query with a parameter in Excel allows for amazing flexibility. The use cases are endless and can benefit so many types of Excel work across industries – if you are querying any data source, such as a database, even an Access database, then parameterized querying in Excel can truly simplify your workflow. I am going to cover SQL queries specifically, but please comment below if you would like a tutorial on using a parameter with other data sources. Nearly anything is possible!

You can also easily use parameters in Power BI, but the process for creating the parameter itself is slightly different – I will cover that in a future post.  The query construction is the same in most cases though. Please note that this post assumes you are at least moderately familiar with SQL querying and Power Query. Your mileage may vary depending on your situation.

If you found this post because you are encountering a firewall error when incorporating a parameter, skip right on to that section below (click here) for the solution. After much trial and error, I have found a way that works to overcome this issue harnessing the power of query folding. 

The Solution: Passing a Parameter to a SQL Query

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • In any worksheet, add a new table, with a single record and column.  I highly recommend the Header clearly clarifies what the parameter is so there is no confusion in identifying it.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range. 
  • When the query editor opens, if necessary, change the data type (in my case I need it to be text).  This is important!
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.  
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

This is how the table is situated in my worksheet (1 is the parameter/the value I will change as needed):

This is how it looks in Power Query after all steps above were completed (I named the column from Enter Store: to Store to make it cleaner (this is an optional step):

Open or Add your SQL Server Query in Power Query

Add your query into the Advanced Editor.

let
Source = Sql.Database(“your connection name”, “your database name”, [Query=”

your query goes here

“])

Create the SQL query with your parameter:

Here’s my simple query example to demonstrate how to build the query with the parameter. Please substitute your table name and fields, as well as your parameter name.

With the parameter in the WHERE clause

“Select  * FROM StoreDetail.Store WHERE Store =” &SParameter&”“

  • Simply build your unique SQL statement in Power Query following my example above, adding your parameter into the query with quotes and the & (ampersand) sign surrounding the parameter name.
  • Check the results and modify as needed.
  • If your query has no additional clauses or criteria, simply put quotes and one & sign ahead of the parameter, and then one after followed by two double quotes to close the query statement.

 Additional criteria in the WHERE clause

Here is an example where I have additional criteria in mine, filtering on the OpenDate field; after the second quote, add a space and then continue on with the rest of your query.

Select  * FROM Storelist WHERE Store =” & SParameter &” AND OpenDate < GetDate()-365“

This is an example of the properly formatted parameter looks in the Advanced Editor in Power Query (I have built out my query further with the Group By clause – this is optional if it doesn’t apply in your case).

If you are running into issues with the query, you may need to add in single quotes surrounding the parameter (inside of the double quotes – “WHERE Store =‘” & SParameter & “‘;”), or possibly # signs surrounding your parameter inside of the double quotes, if your parameter is a date.  You may have to test with and without these depending on your unique scenario.  

If the query is successful, you can proceed with saving it or adding in additional steps in Power Query if you are further filtering or transforming the resulting data you pulled.

BONUS Information – if you are wondering what GetDate() is, this is the MS SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well if you are looking for dynamic dating.

After updating your parameter in the worksheet table, Select Refresh All from the Data menu and your updated query will run and return the results accordingly!

Managing Privacy Levels in Power Query/Excel

VERY IMPORTANT: If you are sharing your workbook with others, you may need to edit the Privacy Levels in order for the queries with parameters to work for them. Read on for these important steps.

In Excel Power Query, there are security measures in place to help prevent data from being shared unintentionally. When combining query sources by using the parameter (a value in your Excel file with a SQL server source), many times this security comes into play.

If other users are having an issue refreshing the query/queries where you have added a parameter into the SQL query, please have them follow these steps. I actually copy and paste this right into an Instructions tab of each of the shared workbooks containing parameters that I create for my clients and for colleagues.

  • Go to Data, click Queries & Connections.
  • Your query list will show up in the panel on the right.
  • Right click any query in the list and choose Edit.
  • A window will open. Click File, then Options and settings.
  • Choose Query Options.
  • Click Privacy, which is on the menu on the left, at the bottom.
  • Select the radio button for the Ignore the Privacy Levels and potentially improve performance option.
  • Click OK to save.
  • Click Close & Load in the Power Query Editor window (at the top) to exit the settings.
Parameter to a SQL query (privacy settings)

Troubleshooting/Alternate Method – Firewall issue

If you run into this dreaded error: “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”, then you will need to use this alternate method.

If you received the firewall error above, you can still use your parameter, but you will instead need to use the parameter as a filter in a subsequent step.  Now you may be possibly thinking – my table has thousands (or even millions) of records and I don’t want them all pulling in – don’t worry, this is the beauty of the query folding process when it works properly in Power Query.

Without the parameter in the WHERE clause

Select * FROM StoreDetail.Store WHERE OpenDate < GetDate()-365

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!

Why Can’t You Include the Parameter in the SQL Statement?

In the SQL statement, in most cases we will want add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot always successfully put the parameter directly into the SQL code/query. The challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).  Unfortunately some organizations have policies that disallow this. Sometimes it is an Excel version issue.

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Do You Need Personalized Help and Custom Solutions?

If you get stuck or you would like to explore solutions and automation possibilities, please can reach out to me for help as I do offer consulting services as time allows.  I have over 20+ years’ of expert level experience delivering excellent, custom, strategic solutions in Excel, BI, Access, SharePoint and more. 

I have been called a guru and hero more times than I can count, as I am a great listener and truly have a knack for asking the right questions to understand unique business challenges. I am very passionate about crafting tools and processes that work for users of all levels and experience. 

Reach out today and let’s discuss how I can help you and your business!

I also offer one-on-one tutoring for customized learning and upskilling. Visit my consulting page and send a message if you are interested.

Other Excel Resources

Also, consider checking out some great Excel resources on Amazon!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to make a purchase, which will help cover the cost of hosting this website. 

Please bookmark and subscribe!  I am actively working on adding new, relevant content to help others out! Thanks so much!

Subscribe via Email and Stay Up to Date!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Buy Me A Coffee/Support the Blog 🙂

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Thanks so much for supporting my blog and for helping others too!

Convert Date in Access – Serial Number, Text String, First of Month, End of Month, etc.

Posted on by bonbon

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month, Date to Text. It is fairly common when compiling date data that we need to convert it, whether for use in other apps (like Excel), outputting in a nicely readable format (Word, PDF, etc.) or to enable joins to other tables, possibly even in other systems. 

MS Access will not automatically convert dates into different types from a menu, so I’ve created this handy post to quickly and easily assist you with doing so quickly.

INSTRUCTIONS

To begin, you will create a new field in your query, and then continue with the conversion below that applies in your scenario. 

Helpful hint: Substitute [yourdate] with the name of the field that you are converting. In the images, the date in my table is called Journal_Date, so this is the one I am converting in each scenario. You should use the actual field name in your table.

To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:

Cdbl ([yourdate])

convert date in access to serial

To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:

Cdate ([yourdate])

convert date in access - serial to date

To convert a text or string date to the date/time format, try:

DateValue ([yourdate])

To convert a date to the first day of the month:

DateSerial (year([yourdate]),month([yourdate]),1)

Access first of month conversion

To convert a date to the last day of the month/end of month:

DateSerial (year([yourdate]),month([yourdate])+1,1)-1

Access end of month conversion; Access last date of month conversion

You can also convert a date/timeformat to a string – here is one example:

Format([yourdate],”mmmmyyyy”)

This would render 7/1/19 in your date field as July2019.

Access Date to Text Format Convert Date in Access

More information on the Format function and syntax can be found on the Microsoft Office Support site if you are looking to output the text differently.

Conclusion

Please feel free to contact me or comment if there are other tutorials/how-to articles you would like to see. I’d love to help you out.

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Subscribe for more great content!

multiply an entire column

Multiply an Entire Column by a Number in Excel (without using a formula)

Posted on by Annie D

Are you looking to multiply an entire column or range by a number? Get ready for a quick and easy tutorial!

You could always use another column and have the formula to do this, but this way is quick and easy if you do not plan on changing the values.

  1. All you need to do is have the number that you want to multiply in a cell (any cell).
  2. Now copy this cell and paste as special in the cells/range/column where you want to multiply it.
  3. In the Paste Special dialog box, choose the multiply option (in the Operation section) and click OK.  Your column/cells/range should be multiplied!
  4. If desired, can delete the cell that has the value which you multiplied by.

The resulting data is static/fixed so there’s no need to change any formula to value after multiplying using the paste special function.

Multiply An Entire Column

As always, I hope this helped you! Simple solutions are the best!

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!

Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution

Posted on by Annie D

Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.

Easy step by step instructions below.  See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.

Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.  

Criteria Table

  1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
  2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
  3. In the Power Query window, select the Transform menu and click convert to list.
  4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

  1. Add or create the list/table that will ultimately be filtered.
  2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
  3. In the Power Query window, click the Add Column menu, and select Custom Column.
  4. In the window that opens, type this:

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

  5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
  6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
  7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
  8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
  9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
  10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

 

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

  1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
  2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

  3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
  4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

View on YouTube

FILTERING-OUT-CONDITIONAL-LIST-1-1Download

Create A Gantt Chart In Power BI With A Matrix Visual

Posted on by Annie D

Managing projects effectively requires clear visuals that communicate timelines and task progress at a glance. While Power BI offers many visuals, building a Gantt chart using the built-in Matrix visual is a powerful technique for showing detailed project timelines without needing custom visuals. This blog post walks through the key concepts and steps for creating a Power BI matrix Gantt chart, perfect for project managers and data analysts alike.

What is a Matrix Gantt Chart in Power BI?

A Gantt chart visually represents project schedules by showing tasks as bars mapped over calendar dates. Unlike using custom Gantt chart visuals, you can cleverly use Power BI’s matrix visual combined with DAX formulas and conditional formatting to mimic a Gantt chart. This approach is advantageous because it leverages native visuals without extra installation, is highly customizable, and integrates seamlessly with your existing data models and filters.

Why Use a Matrix Gantt Chart?

  • Native Power BI Visual: No need for third-party downloads or licensing.
  • Dynamic and Interactive: Integrates with slicers and filters for dynamic timeline updates.
  • Detailed Task View: Offers drill-down capabilities across project hierarchies.
  • Full Control Over Design: Customize colors and styles through conditional formatting rules.

Step-by-Step Guide to Creating a Power BI Matrix Gantt Chart

  1. Prepare Your Data Model
  2. Your dataset should include at least these columns:
    • Project or Task Name
    • Start Date
    • End Date or Duration(Optional)
    • Task Status or Category
    • You also need a Date table in your data model to serve as a timeline backbone.
  3. Establish Relationship sConnect your Date table to your Project table using the Start Date (and possibly End Date) fields to enable time intelligence across visuals.
  4. Create a Matrix Visual
    • Place Project Name (and subcategories if needed) in the Rows.
    • Place Date from the Date table in the Columns.
  5. Create a DAX Measure to Highlight Task DurationWrite a measure that returns 1 if a given date falls within the task start and end dates and 0 otherwise. This will be the basis for your Gantt bars.Example simplified logic: TaskActive = IF( SELECTEDVALUE('Date'[Date]) >= MIN('Projects'[StartDate]) && SELECTEDVALUE('Date'[Date]) <= MAX('Projects'[EndDate]), 1, 0 )
  6. Apply Conditional Formatting Use conditional formatting on the Matrix’s Values field. Format the background color to:
    • Show a distinct color (e.g., gold or blue) when the TaskActive measure equals 1. Show a lighter or neutral color when 0.
    This creates the visual bars of the Gantt chart within the matrix cells.
  7. Enhance Your Visual
    • Turn off subtotals for clarity.
    • Use slicers to filter projects or timelines dynamically.
    • Add tooltips with task details.
    • Customize colors by task status or category using additional measures and formatting rules.

Benefits of Using a Matrix Gantt Chart in Power BI

  • Cost-effective: No licensing or external visuals needed.
  • Integrated experience: Aligns with your Power BI reports and dashboards perfectly.
  • Scalable: Works well from small projects to complex multi-phase portfolios.
  • Customizable: You control colors, interactivity, and granularity.

By leveraging Power BI’s matrix visual combined with smart DAX measures and conditional formatting, you can build a robust, customizable Gantt chart tailored to your project management reporting needs. Start experimenting with your project data today and unlock richer timeline insights right inside Power BI!

Other Options/Resources

For those interested in alternative approaches, there are also custom Gantt chart visuals available in Power BI Marketplace, but the matrix method provides unmatched flexibility and control for many project reporting scenarios.

Delete Rows Based on a Cell Value (or Condition) in Excel [With and Without VBA]

Posted on by Annie D

Managing data efficiently in Excel often means removing unwanted rows that meet certain criteria—such as rows with specific text, dates, numbers, or even partial matches. Whether you’re cleaning up a small spreadsheet or preparing a large dataset for analysis, understanding how to delete rows based on cell values will help you keep your data tidy and relevant. This guide explores multiple techniques to remove rows in Excel according to specific cell values, making your data management tasks faster and more accurate regardless of your proficiency with Excel features or VBA automation.

This tutorial explains several methods to remove rows from an Excel worksheet based on the value in a specific cell or according to set conditions.

Here are four different approaches you can use to delete rows depending on their cell values:

  1. Using Filters:
    Apply a filter to your data, select the criteria you want to remove (for example, rows where “Status” is “Inactive”), and delete all the filtered rows at once.
  2. Sorting Data:
    Sort your data by the column you want to filter (e.g., sort by “Department” so all “Sales” records are grouped together) and then delete all the matching rows in one go.
  3. Finding Cells with Specific Values:
    Use Excel’s “Find” feature to locate cells with a value like “Expired”, select those rows, and delete them all together.
  4. VBA Automation:
    Automate row deletion by using a VBA macro that filters and deletes based on your criteria (e.g., remove all rows where “Order Status” is “Cancelled”).

Tip:
Choose the method that best fits your dataset’s structure and your workflow. Remember, deleting a row removes everything in that row—including all data to the left and right. If you only want to clear certain cells but keep the row and other information, consider using the filter and dummy column trick, or manually clearing cell contents instead of deleting the row.

Wildcard Matching Example:
With Find and Replace, you can use wildcards for powerful matching. For instance, to find every region ending with “East” (such as “North-East” or “South-East”), type “*-East” (the asterisk stands for any sequence of characters).

Example VBA Codes

Delete All Rows Where “Status” is “Inactive”:

textSub DeleteRowsWhereInactive()
    ActiveCell.AutoFilter Field:=3, Criteria1:="Inactive" 'Assumes the Status column is column 3
    ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub

Prompt User before Deleting Rows Where “Order Status” is “Cancelled” Without Deleting the Entire Row:

textSub DeleteCancelledStatusCells()
    Dim MsgboxAns As Integer
    ActiveCell.AutoFilter Field:=4, Criteria1:="Cancelled" 'Assumes Order Status is column 4
    MsgboxAns = MsgBox("Are you sure you want to delete these cells?", vbYesNo + vbQuestion)
    If MsgboxAns = vbYes Then
        ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
    End If
End Sub

Delete Rows in an Excel Table Where “Department” is “Support”:

textSub DeleteRowsinTableForDepartment()
    Dim Tbl As ListObject
    Set Tbl = ActiveSheet.ListObjects(1)
    ActiveCell.AutoFilter Field:=2, Criteria1:="Support" 'Assumes Department is column 2
    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub

**#Excel #ExcelTips #A #ExcelTutorial

Let me know if you need more tailored code or examples for your data!

Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial

Posted on by Annie D

In this tutorial we cover how to create a cross selling matrix.

This requires a full understanding of the concept of ‘context’ and much more.

Some advanced DAX formulas are covered in detail, especially table functions.

Plenty of great techniques to learn about during this one.

Enjoy!

Sam

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – http://bit.ly/2SGof6C

FREE COURSE – Ultimate Beginners Guide To DAX – http://bit.ly/2AyEb3P

FREE – Power BI Resources – http://bit.ly/2C2aBn3

FREE – 60 Page DAX Reference Guide Download – http://bit.ly/2AyEc7T

Learn more about Enterprise DNA – http://bit.ly/2RacRD3

Enterprise DNA Membership – http://bit.ly/2AAqa5u

View on YouTube

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

Posted on by Annie D

Learn how to deal with Power Query Error: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Two solutions are presented in this video.
Download Files: Excel Start: https://ift.tt/2L7OwpO
Zipped Folder: https://ift.tt/2PShOvY
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework – these are problems for you to practice your new M Code skills:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS

Chris Webb’s blog about this topic: https://ift.tt/2NATkG3
Ken Puls blog about this topic: https://ift.tt/2PShRb8

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Top 5 Copy Paste Functions in Excel – Values, Formatting, Special and More!

Posted on by Bb

Top 5 Copy Paste Functions in Excel – A Detailed Guide

The copy and paste functions in Excel save time and effort, since there’s no need to retype information that already exists.  In  this post,  I will cover and explain the different copy and paste operations that Excel offers.

There are a million reasons under the sun to copy and paste in Excel.  You may want to duplicate information in one column, another sheet or even another workbook.  Perhaps you are copying and pasting data from another source, such as MS Access, your web browser, or a PDF document.  Maybe you are moving data around within a worksheet or workbook.

Copy vs. Cut in Excel

When you select copy within Excel, you are telling the program that you would like to duplicate the information.

When you choose cut in Excel, you remove the data from where it currently exists, and place it elsewhere.

How to Copy

To begin, you must select the data that you would like to copy.  This will move the source data to the clipboard, which is a temporary holding place in the background.

Highlight the data.  To copy using the mouse, right click, and select the copy option.   You may also use the keyboard shortcut – Ctrl + c.  Click in the destination cell.

Paste Options Explained

Copy Paste Special
Copy Paste Menu in Excel

PastePaste – If you would like to paste your data as is, with no changes, right click and select this icon.

Excel ValuesPaste Values – If you’re copying a formula, but only want to paste the results, select this icon, with the numbers.

Excel Paste Formulas Paste Formula – Say you wanted to paste just a formula or set of formulas, you would choose the option with the “fx” in the icon.  Note that depending on what the formula is doing, the result may be different from the original.

Excel Transpose PastePaste and Transpose – The transpose option allows you to change the orientation of your data.  Let’s say you had four items listed vertically.  Using the transpose option would allow you to paste the items horizontally, or vice versa.

Excel Formatting PastePaste Formatting – Excel gives you the option of pasting the formatting only.  If you had some nice shading, or conditional formatting and wanted to copy that, but not modify the formula or data of the target cell or range, then paste formatting would be the option to choose.

Excel Link PastePaste Link – You can simply paste a link to, or reference to the source as a formula or formulas if you are copying multiple cells.  Hint: If you are copying only one cell, by default, the reference will be absolute.  This means that you can move the formula around, but the cell(s) it is pointing to will not change.  When copying multiple cells, the result will be relative references.

 

Save

Save

Save

Save

Save