Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to import and filter sql server table by list in Power Query Advanced Editor. Let’s get started
Video
Sample Data
In the captions below, we have main dataset in the SQL Server Management Studio located under the US_States_Data and Dataset Database and Table respectively
In addition, as seen below, we have Largest city dataset in Excel that is directly associated with the Largest City column in the SQL Server.
Import SQL Server Table to Excel
The first thing we’d wanna do is to connect to the SQL Server Dataset Table using Excel’s Power Query. To do that:
- In the Get & Transform Data group of the Data tab, click on Get Data
- In the Expanded option, select From Database and From SQL Server Database
- In the SQL Server Databse dialogue box, type in the Server credential which we have pasted as seen below
- In the Navigator dilaogue box, click on the expand icon right of the US_States_Data databse and select Dataset table as seen below
- Click on Transform Data.
As seen below, the dataset is successfuly imported into the Power Query from SQL Server managment studio
Next, we want to load the dataset in the current workbook into the Power Query also. To do that:
- In the Powery Query’s Home tab, click on Close & Load To…
- In the Import Data dialogue box, select Only Create Connection
- Click OK
- Then, right-click from the Largest city dataset and select Get Data From Table/Range
Convert Table to List
Having imported the Largest city data from Excel to the Power Query, we need to convert the Largest city from Table to List. To do that:
- Select the Largest City query
- In the Any Column group of the Transform tab, click on Convert To List
As seen in the screenshot below, the data is now in Power query list and we can easily use it in the list.containes m function to filter the corresponding Largest city table.
Power Query M Code in Advacned Editor
We want to write the m code that will do the magic in the Advanced Editor. The Advanced Editor is located in the Query group of the Home tab as seen below
Therefore, click on Advanced Editor
In the Advanced Editor dialogue box, we want to write a single line of code using assigned variable name in the let function and then, we are going to use Table.SelectRows M function to select the dbo_Dataset table, use each after the first comma, and then, we are going to wrap list.contains m function. In the list.contains function, we will point to the list we created early and which is the first required argument and then, we point to the Largest city column from dataset table. Finally, we will close the list.contains function and use equal to true and then close off the brackets
So, as seen in the screenshot below, we have written the sinle line of code where we assigned myVariable inside the let function. Then, we passed the myVariable in.
- Click OK
From the caption below, we have a prompt, “Information is required about data privacy”
- Click Continue
- In the Privacy Levels dialogue box, select Public from the Current Workbook dropdown
- Click Done
From the screenshot below, we have the final result
See you in the next video
