If this is the case, the column’s row entries will display the text Table and the data type icon. Preview or Navigate to Table ObjectsĪ column in your query might contain table objects. This will delete that step and all query steps after that step. In the Applied Steps window pane, right click on the first step you want to delete and then select Delete Until End from the menu. If you’re able to pin point at which step your query started going wrong, then you can delete that step and all steps after to start over (without fully starting over).
Sometimes when you are building a query with a lot of steps, you end up going down the wrong path. Here are some tips and tricks to help you get the most out of this incredibly useful and easy tool. If you haven’t heard of power query and the awesome things it can do, or you want to fully understand it better, then check out my Complete Guide to Power Query beforehand. It’s also the same technology that’s used in Power BI, so you’re learning two in demand data skills at the same time! The best part is, it’s built right into Excel 2016 or later. It’s going to save you time and effort if you put in the small amount of time to learn it. Power query is amazing data transformation tool! It allows you to import and transform data with ease and helps to create repeatable and robust procedures for your data. This entry was posted in Excel, Excel Add-ins, General, Monkey Tools, Office 2016, Office 2019, Office 365, Power Query by Ken Puls. The rest of the query will still work, as it drills in to the list of years, so we don't even need to remove this new column.Īnd just like that, we can now use Excel tables to filter a Power Query: This now becomes a pretty easy fix:Īnd you're done. But look what happens when you filter to only a couple of years, then edit the YearFilter Query and select the Source step: Boom! We can see which rows are visible (indicated with a 1) and which are hidden (indicated with a 0). The weird part, if you've never done this before, is that all the visible rows in Excel will always show a 1.
We want to use the filter on the YearFilter table in Excel to filter our Power Query. The column refers to the column of the Sales query (which flows through from the original data).The Filtered Rows step had to be adjusted manually to add the List.Contains function.The Filtered Rows step filters to include any item that is in the list generated by the YearFilter list.It references the Data query (no new data is added here).The important things to notice about this query are:
The YearFilter query is a little more complicated, as it pulls the data, removes duplicates, and then drills down into the Year column (right click the header -> Drill Down), resulting in a unique list of the Years:Īnd finally the Sales Query, which - shown in an indented and 'colourfied' format thanks to MonkeyTools QuerySleuth - looks like this: The Data query is a fairly simple staging query, pulling the data from the Excel table on the left, setting data types, and loading as Connection Only. The data footprint I'm working with looks like this: While Power Query can't read a filter from an Excel table natively, there is a cool little trick that you can do to flow that information through though.
EXCEL QUERIES AND CONNECTIONS ROWS LOADED 1 ERRROR HOW TO
A question came up in the Excelguru forums today about how to use Excel tables to filter a Power Query.