Microsoft Power BI is one of the leading software in the business analytics software world, but there is always a room for improvement. The Microsoft Power BI professionals have certain opinions on removing the duplicates with power Query, opening from default files, parsing the data with M and Audit log storage.
Matt Allington, a Microsoft MVP, recently expressed his views on the Excelerator BI blog about removing the duplicates with power query. He prefaced his views by giving an example of a customer who was in need of loading one record per customer from customer sales orders. Generally, one would approach this by sorting the tables by both customer key column and descending order, but it isn’t a viable solution for the power query, the order dates won’t be right this way. According to Allington, the solution to this is to load all the data into a memory to force a correct output, the method was developed by Chris Webb and Imke Feldman by borrowing the steps from M code.
Moving towards opening the Power BI with a default file. David Eldersveld shared a tip for all the Power BI users. If the users need to open the same PBIT template whenever they open up the Microsoft Power BI, they can do that with the help of Microsoft Configuration Manager, by manually installing the shortcuts to achieve the consistent startups. The users can copy the existing Power BI shortcut on a desktop and reconfigure the path to the target and save the entire thing.
Chris Webb shared an M function in order to make the data formatted by Power Query Diagnostics in a way that it can be visualized correctly in the Power Bi decomposition tree visual. The technique is effective to summarize the data, but at the same time, it removes large amounts of granularity. An alternative M function can be used to counter this issue; A power query diagnostics function that returns the table which displays one row per query. For better and easier viewing, Webb proposed a parent-child column display with an added child rows column, which can house a child related each operation. According to him, we can either click the link in the table column in order to navigate to the table containing all the child operations, or we can just add the new rows onto them with the help of expand aggregate button. He personally prefers the former one. Nested tables provide an easier way of navigating a parent child operation structure similar to what we have in the decomposition tree but differs in the way as it keeps all the detailed columns that the diagnostics data provides, and one isn’t bound to leave the Power Query Editor.
Now coming to storing the audits in Azure. Audit logs have a shelf life of mere 90-days, we can store the data for longer periods, if we transition to the Azure Blob Storage. It is important that the users have a multi factor authentication setup and the global admin privileges. The azure portal has a function app that can be configured for the subscription that is associated to a resource group, a region can be picked, and the application insights can be set up.
Softwares like iNECTA can help with these different transitions, all these programming can be avoided by simply using iNECTA, it helps with better resource management and efficiency.