![]() SQL Server Row Level Security Deep Dive.Alternatives To SQL-2022 Built-in function GREATEST.What’s Faster: IN or OR? Columnstore Edition.“Oops, I Lost My Indexes in Azure SQL DB.”.Office Hours at Breiðamerkursandur, Diamond Beach.Announcing SQLBits 2024: 19-23 March, Near London.Upcoming SQL Server Events – SQL Saturday #407 Vancouver and 24HOP.Conversations with Impact – the importance of stories.Discovering SSRS Report Parameters using PowerShell.Windows 10 and Visual Studio 2015 Jumpstart and Resources.Automate SSRS Report Generation using PowerShell.Getting maximum consecutive years in T-SQL using Common Table Expressions (CTE).sqlbelle’s data adventures (Tableau/SQL Server tutorials on YouTube). ![]() NET Analytics Ebook Metadata Misc Scripts PASS PASSWIT Sample Code SharePoint SMO Speaking Engagements SQL Reporting Services SQL Server SQL Server Powershell SQLXML SQLXML How To SSIS SSRS T-SQL T-SQL Tips and Tricks Tableau Tableau Certification Tableau Tips Tools troubleshooting Visualization vmware Women In Technology XML XSD Recent Posts Will come in quite handy when working with M. Matt Masson just posted a Notepad++ language file for M (Power Query’s formula language). Your final worksheet should look like this: (I am sure there is a better way to automate this, but I will leave that to a later post, once I get more familiar with Power Query and M) We need to use these as values for the rows. Table 1 Eastern Conference – Metropolitanįor the Conference column, the value of the first two sets of 8 would be “Eastern” and the value of the next two sets (one with 8, another with 7) would be “Western”įor the Division column, these have been imported as column headers. After we Load & Close, we can adjust the values from the spreadsheet.Īfter the columns are added, Load & Close and adjust the values from the spreadsheet. Since in the Query Editor we cannot manually change values, we can temporarily assign a value first for the new columns. Add two new columns called Conference and Division. These are the current standings, so we will just call this Standing.Ħb. Once all the tables are loaded, you will see that some of the fields are formatted weird, so we need to do some cleanup.Ħ.Double click on Append3 from Workbook Queries pane to edit the data set.Ħa. In the screenshot below, it’s the “Append1” query. You should see this result set under the Workbook Queries. Just click on Close & Load to load this to the spreadsheet.ĥ.To load the next tables, you will need to set the primary table to the final result set that was returned. When you click OK, a query editor pops up. ![]() Set Table 0 as primary, and append the other tables to it, starting with Table 1. Click on Load.Ĥ.Once the tables are loaded, click on Append. Table 2 is Western Conference – Central, and Table 3 is Western Conference – Pacific.ģ.Check the checkbox to select multiple items, and select Table 0 to 3. Table 1 is also Eastern Conference – Metropolitan. There’s Table 0, which corresponds to the first table in the web page for Eastern Conference – Atlantic. Once you click on OK, you will notice in your Navigator pane on the left a number of tables retrieved by Power Query. Since we have the URL, we can go ahead and use the “From Web” ribbon and paste in the URL Getting this data pre-Power Query would have been possible, but would have required some nifty programmingĢ.Open Excel and go to the Power Query tab. The data is deeply embedded and formatted in HTML tables, as can be seen in the screenshot below. This is my first stab at it, so I will probably look back at this post a few months from now and see where I could improve the process.ġ.First thing to do, after installing and setting up Power Query, is to get the URL for the NHL standings site. That demo really piqued my interest (I like the Canucks too, but they keep on breaking my heart), so I wanted to try it out myself. One of Tessa Palmer’s demo was a shout out to the Vancouver Canucks and how she has kept track of the standings of the Canucks by using Power Query. Last week at the PASS Summit 2014, I attended Matt Masson and Tessa Palmer’s session on Power Query Deep Dive.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |