Analyze Data Structures

In general, the data holds its predefined table and column names, when we import data from multiple sources into Power BI Desktop. Indeed, you may wish to change these names for maintaining the consistent format and create meaningful data structures for a user. Moreover, you can use Power Query Editor in Power BI Desktop for making name changes and simplifying the data structure. 

Let us suppose the columns have been already renamed, and you are required to examine the names of the queries (tables) to determine if any improvements can be made. Further, you may need to review the contents of the columns and replace any values that require correction. 

How to rename a query?

Changing uncommon or unrelatable query names to more specific and familiar names is a good practice for a user. Example, while importing a product fact table into Power BI Desktop with query name as FactProductTable, is not clear so you may change it to a simpler name, as Products.

• Firstly, in Power Query Editor, in the Queries pane to the left of your data, select the query that you want to rename.
• In the second step, right-click the query and select Rename. 
• Then, edit the current name or type a new name, and then press Enter.

How to replace values?

In Power Query Editor, you must use the Replace Values feature to replace any value with another value in a selected column. 

Steps to replace values –

• Firstly, in the Value to Find box, enter the name of the value that you want to replace
• Then in the Replace With box, enter the correct value name and then select OK.  Note, in Power Query, we cannot select one cell and change one value, as performed in Excel.
• Now, you can review the list of steps that you took to restructure and correct your data in the Query Settings pane. 
• Next, when you have completed all steps that you want to take, you can select Close & Apply to close Power Query Editor and apply your changes to your data model. 
• Though, you can take further action to clean and transform your data.

How to replace null values?

There are cases when you might find that your data sources contain null values. For instance, a product export amount on a purchase order may have a null value considering it’s synonymous with zero. So, if the value stays null, the averages will not calculate correctly. Therefore, it is suggested to change the nulls to zero, which will produce a more accurate export average. In the same vein, using the same steps used earlier will help you replace the null values with zero (‘0’). 

How to remove duplicates? 

We can remove duplicates from columns to only keep unique names in a selected column by using the Remove Duplicates feature in Power Query. 

• Firstly, select the column
• Then, right-click on the header of the column
• Select the Remove Duplicates option. 

Best Naming Practices

Indeed there are no fixed rules or specific naming conventions for tables, columns, and values have no fixed rules, yet some of the suggested practices are –

• Firstly, use the language and abbreviations that are commonly used within your organization as common terminology. 
• Secondly, it is suggested to give tables, columns, and measures descriptive business terms and replace underscores (“_”) with spaces.
• Thirdly, you should be consistent with abbreviations, prefaces, and words like “number” and “ID.”
• Then avoid using too many short abbreviations as they may cause confusion within your organization. 
• Also, remove prefixes or suffixes used in table names
• When replacing values, values that are too long might be difficult to read and fit on a visual. Values that are too short might be difficult to interpret.
• Lastly, you should avoid using acronyms in values, given that the text will fit on the visual.