For several years now, Power BI and Excel have been developing side by side with the idea that analysts do not save all their tables exclusively on a local computer, OneDrive or SharePoint repositories, but that they can also download them from Power BI reports that are published on the company’s tenant. If you use a Microsoft365 subscription that is connected to a company account, you can easily retrieve such tables.
For a certain period of time, when I need to split data in a column, my first choice is to use the Power Query add-in and the Split Columns option. However, there are still users who do not know how to use it, so I remembered that for this purpose we can also use the Excel option Text to Columns. In this “recipe” you will find out a practical way how to split one column into several.
Imagine this scenario: you register customers in an Excel spreadsheet, specifying their ID and then the products they use. Which customer uses which products? One way to get this answer is to create a pivot table, and then display customers, products and the number of products. The disadvantage of this report is that pivot tables always aggregate data. What if we only need a table with customer ID in one column and a consolidated list of products in another?
The unique identity number of citizens (JMBG) was created in the Socialist Federal Republic of Yugoslavia, and is still used today in the Republic of Serbia, for the purpose of identifying citizens. It is a mandatory datum in the identity card, driver’s license, health card, passport and all other important documents. Its first 9 digits indicate the date of birth. How to calculate the age of the JMBG holder based on this data?
