Azure Databricks has become the go-to platform for data engineering and analytics, while Power BI remains the most powerful visualization tool in the Microsoft ecosystem. Connecting Databricks to Power BI bridges the gap between your data lakehouse and business users, enabling real-time insights from curated Delta tables. In this blog, we’ll walk through the process of securely connecting Power BI to Databricks, covering both DirectQuery and Import mode, and sharing best practices for performance and governance. Architecture Overview The connection involves:– Azure Databricks → Your compute and transformation layer.– Delta Tables → Your curated and query-optimized data.– Power BI Desktop / Service → Visualization and sharing platform. Flow:1. Databricks processes and stores curated data in Delta format.2. Power BI connects directly to Databricks using the built-in connector.3. Users consume dashboards that are either refreshed on schedule (Import) or query live (DirectQuery). Step 1: Get Connection Details from Databricks In your Azure Databricks workspace:1. Go to the Compute tab and open your cluster (or SQL Warehouse if using Databricks SQL).2. Click on ‘Advanced → JDBC/ODBC’ tab.3. Copy the Server Hostname and HTTP Path — you’ll need these for Power BI. For example:– Server Hostname: adb-1234567890123456.7.azuredatabricks.net– HTTP Path: /sql/1.0/endpoints/1234abcd5678efgh Step 2: Configure Databricks Personal Access Token (PAT) Power BI uses this token to authenticate securely.1. In Databricks, click your profile icon → User Settings → Developer → Access Tokens.2. Click Generate New Token, provide a name and expiration, and copy the token immediately. (You won’t be able to view it again.) Step 3: Connect from Power BI Desktop 1. Open Power BI Desktop.2. Go to Get Data → Azure → Azure Databricks.3. In the connection dialog:   – Server Hostname: paste from Step 1   – HTTP Path: paste from Step 14. Click OK, and when prompted for credentials:   – Select Azure Databricks Personal Access Token   – Enter your token in the Password field. You’ll now see the list of Databricks tables and databases available for import. To conclude, you’ve successfully connected Power BI to Azure Databricks, unlocking analytical capabilities over your Lakehouse. This setup provides flexibility to work in Import mode for speed or Direct Query mode for live data — all while maintaining enterprise security through Azure AD or Personal Access Tokens. We hope you found this blog useful, and if you would like to discuss anything, you can reach out to us at transform@cloudfronts.com

After PowerAutomate was added in PowerBI as a preview feature, it is finally available for general use. In this blog we are going to use this PowerAutomate visual to export PowerBI data into CSV and upload the CSV on Sharepoint. Open PowerBI Desktop and drag the PowerAutomate Visual, if it isn’t available in the visualization pane update your PowerBI Version. Now add the columns, you need in your CSV file. Click on the ellipsis button on the visual and select edit. Create new flow and select instant cloud flow. A default PowerBI trigger would be created, click on add new step. Search “Compose” action in the search bar and select it as we have to compose PowerBI Raw data first. In the Inputs select PowerBI Data. After composing we have to convert this data into CSV, therefore add “Create a CSV table” step and add previous output. Now we have to upload this CSV into sharepoint so we will add a step to create a sharepoint file. Enter the Sharepoint address, Folder Path, give the file any name desired and put “.CSV” as suffix. Save and close the map. To run the flow, press Ctrl and left click. As you can see the file in SharePoint is created. Thanks for reading, hope this blog helped!

Consider a scenario where a start and end date along with the total duration for a particular task has been given to us. We have to distribute the total duration equally among all dates between the start and end date. We can solve this issue by the combination of Power Query and DAX. Lets see the steps First we need to generate the list of dates from start date till end date. Open Advance Editor As you can see the table I’m working on have two columns for start and end date for a particular task respectively. In Power Query we cannot generate a list between dates so first we have to convert the data type from date to numeric. This can be easily done by right clicking the desired column and changing the data type right away. After the data type of respective columns is changed into numeric, click on the “Add Column” option in the ribbon and select custom column. To generate a list in Power Query the syntax is “starting number .. Ending number” so we apply this syntax in accordance to our needs. The “Number” function is make to sure to take only numeric values to avoid any conflicts. After validating the code press the “OK” button. You can see a new column with lists. Click on the Expand button on the top right of the column. After Expanding the column you’ll see a list of numbers. Since these numbers are numeric we have to again convert them in Date format. This can be done by right clicking on the column and changing its Data Type. As you can see we can see all the dates between start and end dates now. 2. Since we generated the list of dates. We Proceed to distribute duration equally Create a new Calculated Column in PowerBI Desktop Write the following DAX. actual hours = Sheet1[original estimate]/ CALCULATE(count(Sheet1[taskid]),FILTER(Sheet1,Sheet1[taskid]=EARLIER(Sheet1[taskid]))) This code divides the Duration assigned for a task by the count of the total tasks where the task id is same. As you can see the Original Estimate column which is for total duration for a task is equally divided into a new column called “actual hours”. You cant see dates of Saturday, Sunday as I filtered these dates in Advanced Editor itself as they are non working days. This can be modified according to requirement. Thank you for reading hope this article helped

While importing data into PowerBI we can choose the tables we want to load in the data model but we cant choose specific columns, although columns can be removed through advanced editor or by manually deleting them one by one this can also be done without using advance editor and multiple columns or tables can be deleted at once. To do this go into the data model section of your PowerBI report. Now expand the desired table and the press ctrl key on your keyboard and select the columns you want to delete. You can also select multiple tables using the same instruction. After selecting multiples columns or tables right click and select “Delete from the model” option. In the dialogue box that appears click on the “Delete” button. Then click on “Apply Changes” option which will appear in the window and you can see all those columns are now deleted Hope this helps!

Sometimes we would want to ask users about their thoughts , opinions or ask some questions along with a PowerBI report. That is, we would need  to collect information about the user through PowerBI App.  This can be done by creating a Microsoft Form inside PowerBI interface to collect user information. This can be demonstrated as: Go to forms.office.com and create a new form. Insert the Title , Description and add some fields in my case I have created a Test form with a field for giving ratings. Click on share and copy the link. Go to app.powerbi.com , create a new workspace and while creating enable develop a template app option Add your existing PowerBI reports here by clicking “New” option and then click on create app. You can add Name, description and logo to this app also you can choose a theme Click on Navigation tab and enable new navigation builder Click on the  New button on the navigation pane and select link. Enter the Name of the form and enter Microsoft form link Publish the app then select go to app As you can see the form is embedded in PowerBI interface The responses done by users gets saved at forms.office.com This can be used in a scenario where we need feedback for a  PowerBI report Hope this blog helped Note: Not all Power BI users can view and interact with apps. If you have a free license, you can open apps that are stored in Premium capacity and have then been shared with you. If you have a Pro license, you can open apps that have been shared with you.

Introduction: Sometimes while refreshing our dataset in PowerBI or importing new data from existing sources we may encounter OLE DB or ODBC errors as shown in the image below. This might be due to caching issues. To solve this follow the steps: Go to File tab on the ribbon in PowerBI Desktop , then click on About and check the PowerBI version, make sure it is the latest version if not update PowerBI. If PowerBI version is latest, click on the dropdown arrow below the Transform Data button. Click on Data source settings Click on Global Permissions Select the Data Source and click on Clear Permissions and click on close. Click on close and again select the data source as new and enter the credentials. This time the dataset would be loaded without errors. Hope this article helped

In this blog we will see how to fetch Azure Devops Data and based on it create Visualization as per requirements. Step 1: Go to Azure Devops > Go to User Setting > Personal Access Token  Step 2: Once Clicked on Personal Access Token, click on New Token, following popup will appear.  Enter details Name, organization and Expiration and specify Scope.  New popup will appear, and it will generate access token key, copy it.  Step 3: Prepare the OData URL             Sample OData URL: https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/   In our Case, Organization is gvishwakarma Version is  v1.0  Then URL will be https://analytics.dev.azure.com/gvishwakarma/_odata/v1.0/  Step 4 :Open Power BI click Get Data > select OData Feed  Sample Data  Once data loaded in power BI we create visualization as per requirement.  Hope this Helps! 

Introduction The September 2018 Update of Power BI has finally brought to users, the functionality to copy data from a table or matrix in a report. This has been a much-requested feature as one often finds themselves needing to copy a value or columns of data from a report with no option to do so. Steps: Now, if you want to copy a value, several columns  in a table or matrix you can do so by following the steps below: Copying one value :  Select the value->right-click on the selection -> select ‘Copy Value’ -> An unformatted value will now be added to your clipboard. Copying several columns of data: Select all the data in the required columns->right click on the selection and select ‘Copy Selection’.-> The required data and column headers are copied to the clipboard. In a Matrix, when you select one value->right-click-> ‘Copy Selection’-> All measures required are copied along with the value ( as shown in the screenshot below). Conclusion: This is a long-awaited feature of Power BI and will definitely enhance user experience while navigating and using Power BI reports.

The September update of Power BI features an important functionality: the option to drillthough to another page in that report. For example, if a user wants to see detailed data of a product ‘Product 2’, then they can right-click on Product 2 in the main report, select ‘Drillthrough’ and the relevant sub-report to have it automatically filter the sub-report for Product 2. To do this, open your sub-report page and add any filters you require to be applied when the main report drills through to this report. For example, ‘Product’ and select ‘Used as category’. Go to the main report and select (right-click) on the Product you want to use to drillthrough, click on ‘Drillthrough’ and select the name of your sub-report. This will re-direct you to the sub-report, now filtered by the selected product. Additionally, to go back to the main report, one can simply click the back ( <- ) button on the sub-report. This feature enhances the users’ understanding of the data as well as greatly improves user experience.  

In the August 2018 update of Power BI, we can now apply conditional formatting based on field value. This allows us to use a measure or column that contains a color name, a hex name/code and apply that color to the background of the cells and the font in the cells of that column. For example, if I want to use color to signify the product name, we can create a calculated column to assign color names to each product as shown below: Then add that calculated column (‘Color’) to your table, and go to the Conditional Formatting dialog for ‘Color’. Select ‘Background Color’.   Select ‘Field Value’ in the ‘Format by’ drop-down menu. Since I only have one color name per row, I will select First ‘Column Name’, Summarization ‘First’  and click OK. The background color of the cells in the calculated column will now be according to the color name in the cell. If you don’t want to see the color names, repeat the steps but select ‘Font Color’ as shown below. The entire cell will now be filled. If you want to use hex names/codes instead of color names, you can create a calculated measure/column using them as shown below. Now follow the same steps as above to apply conditional formatting to the table using hex codes/names. This can help highlight sets of data easily based on their values and without the need to set rules.