We need to create a connection so that we can pull the data from a text file in Excel. We will use Power Query to import this file under Excel and try to manipulate the same using Power Query itself. Suppose we have a text file in a folder that consists of 1000 rows of data. Ideally, it can be considered an ETL tool that allows you to Extract, Transform, and Load the data into Excel. Power Query can help you in automating the data import task in Excel with minimal effort, and it is so much easier than you can feel navigating through Excel itself. These sources include some relational databases such as Microsoft SQL Server, Microsoft Access, Azure, etc., as well as data files such as Excel, Text, CSV, XML, JSON, etc. Power Query is a powerful excel tool that allows a user to import data from various sources into Excel. net (13) ADO.NET (4) AOP (1) appStore (1) Aspect Orient Programming (1) assembly binding error (1) Asynchronous triggers (1) Attributes (1) Batch (1) c# (17) C# 3.0 (1) C# 3.5 (1) C# 4.Excel functions, formula, charts, formatting creating excel dashboard & others Power Query to Automate the Data Import Process LEFT JOIN Artist ON Artist.MusicLibraryId = MusicLibrary.MusicLibraryIdĪND EntityTypeCode = JOIN Album ON Album.MusicLibraryId = MusicLibrary.MusicLibraryIdĪND EntityTypeCode = JOIN Song ON Song.MusicLibraryId = MusicLibrary.MusicLibraryIdĪND EntityTypeCode = EntityTypeCode IS NOT NULL REPLICATE(' ', LibraryHierarchyLevel) + Song.Name REPLICATE(' ', LibraryHierarchyLevel) + Album.Name With hierarchy we can add a song under an artist without adding another key, just relate the song under the required artist.
If we use foreign keys we'll add another key to song pointing the artist table and than the song table would have sometimes an albumId with a value and artistId with null or vice versa - doable but not so elegant. What if we add a 'single', a 'single' is a song that is a child of an artist not an album. 's not a better solution, just a different one, I can think of two advantages:ġ. So you may ask: "what do I need this complexity? we can add a reference from song to album, from album to artist and that's it." Song.MusicLibraryId = MusicLibrary.MusicLibraryId SELECT = MusicLibrary.LibraryHierarchyIdĪlbum.MusicLibraryId = MusicLibrary.MusicLibraryId Now use these stored procedures to connect all the entities together:Īnd final step, lets see how we can query the hierarchyĭECLARE smallint = 1, smallint = 2, smallint = 3 IF (NOT EXISTS(SELECT AlbumId FROM Album WHERE AND MusicLibraryId IS NOT NULL))Īrtist.MusicLibraryId = MusicLibrary.MusicLibraryIdĪND MusicLibrary.EntityTypeCode = Artist.ArtistId = Similar logic when adding album & song to the library, but this time we'll send the 'parent' artist/album id to the stored procedure: WHERE LibraryHierarchyId.GetAncestor(1) = NULL) (SELECT MAX(LibraryHierarchyId) FROM dbo.MusicLibrary IF (NOT EXISTS(SELECT ArtistID FROM Artist WHERE AND MusicLibraryId IS NOT NULL))ĭECLARE AS HIERARCHYID, AS AS HIERARCHYID Use the hierarchyid methods (GetRoot, GetAncestor & GetDescendant in this case) to get the last child of the root and add the new artist under that id. Now we'll build an helper stored procedure to add an artist, album & song to the library. CHECK CONSTRAINT įill it with some data.and finally start using the hierarchyid to build the hierarchy. The MusicLibrary will contain the hierarchy: INSERT INTO GlobalEntityType (Name) VALUES ('Song') INSERT INTO GlobalEntityType (Name) VALUES ('Album') INSERT INTO GlobalEntityType (Name) VALUES ('Artist') IDENTITY(1,1) NOT NULL,ĬONSTRAINT PRIMARY KEY CLUSTERED The GlobalEntityType table will contain a lookup table for all the entity types: I wanted an hierarchy between different types of entities, so the hierarchy will be handled in the "MusicLibrary" table and the Artist, Album & Song tables will point to it. MSDN tutorials contained samples where the hierarchy was used in the same table, that's great for an hierarchy where the hierarchy is within the same entity like employees sample (manager is an employee who manages other employees.). So I Added a sample DB with Artist, Album & Song tables. Started with my favorite subject: Music Library.
Excel query table with hierarchyid data type how to#
So I sat down just to play & getting to know how to use it. In SQL Server 2008 Microsoft added the hierarchyid data type, one of those things that I had in mind but never got to use, till recently.