Tag: power query

How to Migrate an Existing On-Premises Data Gateway for Power BI

Luke Johnson

A few years back, everyone was excited about the 3D and visualisation aspect of BIM. It seems that more and more, people are getting excited about BIM Data. Many companies are building and adapting business intelligence solutions that somehow connect to or wrap-around the BIM world. One of the most popular tools at the moment is Power BI. It is extremely flexible and easy to set up and use for data analytics.

When you connect the Power BI web service to your own database, you also have to provide some mechanism for Power BI to periodically update the base dataset. The common answer to this question is the “On-premises data gateway”.

When you install the gateway on your own hardware, there are two types of gateway to choose from:

On-prem-data-gateway-install-powerbi

If you are already running a data gateway for Power BI, what happens if you want to transition the ‘gateway’ role to a new machine? Well, you need one key piece of information: the Recovery Key from the gateway when you set it up originally. If you do have this, you can simply ‘take over’ the role from an old machine and apply it to a new machine:

  1. Download the PowerBIGatewayInstaller.exe gateway installer from this page
  2. Choose your gateway type
  3. Choose to “Migrate, restore or takeover an existing gateway”

  4. Enter the information, including the recovery key

  5. Done

If you don’t have a recovery key, you will have to:

  1. Install a completely new gateway:

  2. Switch the gateway used on the web app side (Scheduled Refresh page). You will probably have to do this for every dataset.

Feel free to reply with any of your cool BIM data workflows 🙂

Automatically Import Data From Multiple Excel Files into one Worksheet With Power Query

Luke Johnson

Let’s say you have a set of Excel files and you want to feed that information into one Excel file, to allow you to do summaries or produce Charts. How would you go about it? Here is one way:

Install Power Query from:
https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=85f847dd-369e-4417-b604-6a2f3c673084

Use this function (copied from link below) to import multiple XLSX to one sheet. 

 //Define function parameters  
 (#"Directory containing Excel files to combine" as text,   
 optional #"Name of each Excel object to combine" as text,   
 optional #"Use first rows as headers" as logical) =>  
 let  
   //If the optional Excel object name parameter is not set, then default to Sheet1  
   ExcelName = if #"Name of each Excel object to combine" = null   
         then "Sheet1"   
         else #"Name of each Excel object to combine",  
   //If the optional Use first rows as headers parameter is not set, then default to true  
   UseFirstRowsAsHeaders = if #"Use first rows as headers"= null   
               then true   
               else #"Use first rows as headers",  
   //Get a list of all the files in the folder specified  
   Source = Folder.Files(#"Directory containing Excel files to combine"),  
   //Filter these to only get Excel files  
   OnlyGetExcelFiles = Table.SelectRows(Source,   
                each ([Extension] = ".xlsx")   
                or ([Extension] = ".xls")),  
   //Find the full path of each file  
   FullPath = Table.CombineColumns(  
           OnlyGetExcelFiles ,  
           {"Folder Path", "Name"},  
           Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),  
   //Get a list containing each file path  
   ExcelFiles = Table.Column(FullPath, "Merged"),  
   //Define a function to get the data from the specified name in each Excel workbook  
   GetExcelContents = (FileName as text) =>  
    let  
    //Connect to the workbook  
    Source = Excel.Workbook(File.Contents(FileName), UseFirstRowsAsHeaders),  
    //Get a table of data from the name specified  
    //If the name doesn't exist catch the error and return null  
    ExcelData = try Source{[Item=ExcelName]}[Data]   
               otherwise try Source{[Name=ExcelName]}[Data]   
               otherwise null  
    in  
    ExcelData,  
   //Call the above function for each Excel file  
   ReadAllWorkbooks = List.Transform(ExcelFiles, each GetExcelContents(_)),  
   //Remove any null values resulting from errors  
   IgnoreNulls = List.RemoveNulls(ReadAllWorkbooks),  
   //Combine the data from each workbook into a single table  
   CombineData = Table.Combine(IgnoreNulls)  
 in  
   CombineData

Here are some of the steps to get the function into Excel:

Read more at:
Combining Data From Multiple Excel Workbooks With Power Query–The Easy Way! | Chris Webb’s BI Blog