The #1 Mistake You're Making in Your Queries

so you've created your query and when you hit refresh you get the dreaded the table of the polymers and found error or when you import your files from a folder into Power query instead of all your files being quickly combined you get the elusive the key didn't match any rows in the table message what does that even mean and how do you fix these errors well in this video we're going to work through four different scenarios that shows you how to avoid these types of Errors which are caused by hard coding in your queries and instead I'll show you how to make your queries more dynamic we have these two tables and they're exactly the same and here we have the queries for these tables if there had to be a change in a column name here in our first table let's say sales rep name change to sales rep and we hit refresh we get the column of the table wasn't found error but if the same change happened here in our second table and we hit refresh our query updates to show the changed column name without any errors let's go to our query Editor to find out how we can do this here in our query editor there is no error in our source step but there is an error here now change type step so when we changed our column name from sales rep name to sales rep power query returned an error as all the column names were hard-coded here in the change type step so when power query wants to perform the change type step it searches for the column called sales rep name and as it can't find it it returns an error so we need to make our change type step Dynamic and not have these hard-coded values Kiana formula bar we have our function called table.transform column types that's used to perform our change type step this function requires our first parameter which is our input to be a table which in this case is source and the second parameter needs to be a list which in this case are a column headers and lists are always in curly brackets so we need to make our second parameter of our change type step dynamic as this is where our hard-coded text is first let's duplicate this query let's remove the change type step next here in our home tab let's click on keep rows and click on keep top rows the number of rows that we want to keep is one next in the transform tab click on the drop down for use first row as headers and click use headers as first row so we're demoting our headers when power query performs the demoted header step it performed the change type step we don't need it so let's exit out next in the transform tab click on transpose and we've transposed our table with our column headers in the First Column and the values in the second column let's rename this query to sales data next in the add column tab let's click on custom column and let's type if and select column 2 here from our available columns equals null then type any DOT type else value DOT type and let's select column 2 again and remember to add your parentheses around the second column two as we're using a function valued our type on this column and hit OK and we have a new column that has our data type for each column header let's select column 1 hold down the control key and select our custom column right click and select remove other columns next let's transpose our table again in the formula bar let's click on the effects step and a new custom one step is added and it references our previous step this is the sequence in which M code Works a step always starts by referencing the previous step if you would like to learn more on M code and the advanced editor I highly recommend you watch this video here I'll leave the link in the description also now we need to convert this entire table to a list why do we need to do this let's go back to our sales data not Dynamic query and for our change type step AKA table.transform column types a second parameter needs to be a list and this is the parameter that we want to be dynamic so let's go back to our sales data query and here in our formula bar before transpose table 1 let's type table.2 columns the intellisense brings it up and remember to insert the parentheses around transpose table when as we're applying a function to this and functions always have parentheses just like in Excel and hit enter and we have a list of nested lists and if we look at our icon on the left here it's changed to a list and it's no longer a table let's rename this to CN list for column name list you can name it whatever you like each list has our column name and what type that column is so if we click on one of these lists we can see the contents of that list and if we click on the type we can see what the data type is in this case it's text let's remove these last two steps as it was just to show you the contents of the list let's go back to our sales data not Dynamic query and now all we need to do is go to our change type step and here in the formula bar delete our entire second parameter and start typing CN list let's select it from the intellisense and hit enter and our query is fixed let's test it out let's send this back to excel in our source data let's change amount to sales amount and hit refresh and our query correctly updates our column name so we've now made our change type step dynamic in our next example we have our data in this format and we've created a function that performs a data cleanup and transforms that data to this format here however when we input region 2's data into our function we get this error the column sales rep of the table wasn't found so a step in our function is hard coding the sales rep column which is preventing our function from being Dynamic let's see how we can fix this let's start from scratch and create our function first let's duplicate our region 1 query and call it data cleanup let's remove our change type step as we will perform it at the end next let's remove our MTD cells column as we don't need that in our end query let's rename sales rep to representative as this is the column name that caused our column of the table wasn't found error so renaming the inconsistent column name here will help us have the same column names across our queries as if we compare our region 1 and Region 2 column headers the only inconsistent column name was sales rep in region 1 and rep in region 2. let's go back to our data cleanup query and select our representative column right click and select and pivot other columns in our formula bar let's change attribute to month and value to sales amount so this is the cleanup that we want our function to perform but before we convert this to a function let's check our steps that have hard-coded text and see which of those we need to make Dynamic to prevent our function from failing our removed column step has MTD cells as our hard-coded text but it's the same name in both our tables so we don't need to be concerned with the removed column step our renamed column step has sales rep and representative hard-coded and our unpivoted other column step also has all these as hard-coded text let's make our renamed column step Dynamic and to do this we will use the table dot column names function let's create a blank query so that I can quickly show you what this function does in our formula bar let's type equals table dot column names and in Brackets type the name of our table which is region 1 and hit enter and we get a list of our column names now the column name that we won't return is sales rep so let's type 0 in curly brackets as power query is zero based and hit enter and sales rep is returned so whatever column name is in position 0 is going to be returned let's copy this code and let's go to our data cleanup query and here in the renamed column step let's paste our code over sales rep and hit enter and our query looks the same but it's not we will test it out in a few minutes I'm pivoted as a column step has these column names as hard-coded text but these won't cause any errors as once the renamed column step is applied across our tables all our column names will be the same let's perform a change type step next let's create our function let's open up our Advanced editor and here is the code that power query generates advice let's add a space before let and use where we will tell power query this is a function the first part of our function relates to what our input needs to be let's name the inputs as everything is named in power query just like our applied steps let's call this input sales data and remember the brackets you can name it whatever you like we also need to Define our input type my input will be tables so type as table outside of our brackets type the error operator which indicates the power query this is a function so everything before the arrow operator is our input to our function and everything after the arrow operator is what must be down to that input The Source step brings in our source data which is region 1. let's cut this entire step as we don't need it as sales data will bring in our source data let's also replace Source everywhere else in our query with sales data it's here in the removed column step and we need to replace region 1 in our table dot column names function with sales data as if we don't we're going to get an error when we input Region 2 in our function we've now converted our query to a function let it done and let's test out our function let's invoke our Region 2 sales data and our function correctly performed our data cleanup and we can test it out on our region 1 data and it's correctly performed our function on region 1. now we need to combine our weekly data that's added to our folder the structure of the data is the same the only difference is that each sheet has a different name let me know in the comments if you already know what error we're going to get when we upload this to power query in a blank workbook let's go to data get data from file from folder I've copied my folder path so I'm just going to paste it and click open power query displays the files in our folder which are correct we want to combine and transform our data so let's select that here now combined files dialog box pile query chooses our first file as the sample file so when we connect to a folder to combine all our files you'll see in a few minutes their power query creates a query called sample file which is used as an example to process each file it's important to note that the end results of our end query will depend on the columns that exist in the sample file you can click on the drop down to select any of your files as the sample but let's keep our first vowel which is week one as our sample let's click on the sheet here to select it remember this was not in a table format but that's fine as power query will perform the Transformations for us and click ok and here are all the queries the power query is generated for us to get our end result here which is this table called weekly sales that only a week when data has imported correctly and we have this error here which says the key didn't match any rows in the table and then it gives us the details here so if you guess that this would be our error you were right this error means that power query is trying to access the sheet 2023.0505 in our week 2 and week 3 sheets but this sheet does not exist or let's rather say it has a different name each week to fix this error we need to go to our transform sample file query this query does exactly what it says it performs Transformations on our sample file that we chose which was week one so any changes made to this transform sample file query will be reflected here in our transform file function and this function is then applied to all the files in our folder before combining them into one table so for example in our transform sample file if we are to click on ADD column and go to date man name of month we get our month names here in our transform sample file and if we check our end query it also has our month name so we need to make our changes here in our transform sample file query let's take a look at the applied steps of this query our source step brings in our data and here now navigation step we have the section of code between the curly brackets where our sheet name has been hard coded so to make this section of hard-coding dynamic let's instead replace this entire section of code with just a zero between the curly brackets this zero is the position of the first sheet in each of our Excel files so in Excel sheet1 is position zero sheet2 is position 1 and so forth so we know that the data that we want is always going to be in our first sheet in each of our files so instead of using the sheet name we can rather use the position of the sheet let's click on our end query and we can see that all our files in our folder have been successfully combined and our error of the key doesn't match any rows in the table is fixed let's rename Source name to week and in the transform tab let's click on extract and let's select text before delimiter and let's type the dot and click OK and we've removed our Excel extension and we can send this to excel we have our end query here in Excel which is generated from these three sheets that were combined in power query if one of these sheets had to have an additional column added to it and if we go back to our query and hit refresh our query doesn't update to show the new column let's see how we can fix this we're going to start from scratch so that you can see how we uploaded and combine the data first let's copy the path to our workbook then in a blank workbook go to data get data from file from Excel workbook as all three sheets were in the same workbook and let's paste our path here and click open here in the Navigator tab let's select our folder which is our Excel workbook and click on transform data here in our query editor power query shows us the files that it's found in our workbook the column that has our data is the data column power query is so literal it makes a dictionary look like a joke let's select our data column right click and select remove other columns next let's click on the expand icon and remember to uncheck use original column name as prefix and click OK and we have all our sheets combined next let's filter our region 1 2 and 3 sales data these were the headings in each of our sheets we don't need those and click OK and let's click on this table icon on the left here and click use for Australia's headers and our headers have been promoted we also have the column headers for Region 2 and 3 so let's filter out region and the headers have been filtered out now the step that's preventing new columns from being added to our end query is the expanded data step as it's hard-coded our column names so we need to make this step Dynamic let's click on our second step after source which is the removed at the column step right click and select delete until end and select delete before we combine these tables we first need to get our headers promoted in each table as they're all the way in Row 2 in each of our tables in the add column tab let's click on custom column and let's type table dot remove first in and insert the Open brackets and select the data column and insert the close brackets and hit OK and we've removed our first row now to promote our headers in each of our tables let's go back to our custom column step and after the equal sign let's type table dot promote headers and insert our brackets and hit OK and our headers have been promoted in each of our tables next let's select our custom column right click and select remove other columns now it's really tempting to click the expand tables icon but we're not going to do that as remember that's the step that hard-coded our column names let's instead click on the transform Tab and click on convert to list and our one column table is now in a list format that we can use in our next function in our formula bar let's type table dot combine after the equal sign and insert our brackets this function merges a list of tables into one table this is why we had to convert our tables to a list so that we could input it into this function and hit OK and all our sheets have been successfully combined into n and our column headers have also been promoted let's send this back to excel now in our source data let's add a new column to our Region 2 sheet remember to save and let's go back to our query and hit refresh and now we can see our new column that's been added

Channel: Miss Microsoft

Views: 17,401

Keywords:

Id: MMF3bNXqT2c

Length: 20min 32sec (1232 seconds)

Published: Sat Jun 10 2023