How to Work in Power Query Using ODBC Driver

How to Work in Power Query Using ODBC Driver

1. Connect to an ODBC Source

In Power BI Desktop, go to the Home tab and click on Get Data to connect to an ODBC source.



From the list of available connections, select the DSN connection that you created in the ODBC Administration tool.

You can learn how to set up your DSN connection for FreshDesk by following this link:




After selecting the DSN and clicking OK, you'll see a list of available FreshDesk objects that you can use in Power BI:




You can also provide an SQL query to specify the data you want to retrieve, such as limiting the number of rows or excluding unnecessary columns:



2. Load or Transform Data

At this stage, you can choose to either:

- Load the data directly into Power BI, or
- Transform Data to make changes before loading it.

If you select Transform Data, the Power Query Editor will open, showing a preview of your table. The Query Options will appear on the right, or you can always access them by clicking on Query Options in the View tab:



3. Shaping the Data
Once your data is connected, you can start customizing it. Shaping means changing the data by:

- Renaming columns or tables
- Deleting rows or columns
- Changing data types

For example, if you need to change the data type of a column, select the column (or multiple columns by holding Shift for contiguous columns or Ctrl for non-contiguous columns). Right-click the column header, choose Change Data Type, and select the new type from the options in the Transform section:


Example: Sorting Data

If you want to sort the records by the Phone column, click the arrow next to the Phone column header and select Sort Ascending. The data will be sorted by the phone number, and the Sorted Rows step will appear in the Applied Steps section:


If you want to remove records that don’t have a Phone number, you can delete the rows. Go to the Home tab, select Delete Rows > Remove Bottom Rows. In the dialog box, enter the number of rows to remove (for example, 10), and click OK:



This will remove the bottom 10 rows, and the step "Bottom rows removed" will be recorded in the Applied Steps section.

4. Create Calculated Columns

You can create new columns in Power BI by combining existing data. For example, you can apply a formula to an existing column or create a new column by combining two columns.

To add a new calculated column, go to the Table Tools tab on the left and click Add Column:


Example: Creating a Custom Column

In this example, let’s create a new column called CustomColumn, which extracts the last 5 characters from the Phone column.

Use the formula:  
CustomColumn = RIGHT([Phone], 5):


This creates a new column with the last five characters of the Phone number, which can be helpful for analyzing phone numbers more effectively.