Transform unstructured data into usable insights for Power BI
Power BI (Business Intelligence) is a Microsoft BI tool which is widely used at every level in the organization to make confident decisions, perform ETL logic and visualize data using up-to-the-minute analytics. Here, we are targeting the issue of mixed data values coming from APIs in a single column. Targeted audience– Developer Use Case:, In figure: A column containing the Records and List of records may create issues while further expanding them due to different data types. We can expand the list and record separately, but it will duplicate columns if the data contains a similar structure to this. JSON Sample for reference: { “@timestamp”: “30-11-2020 01:31:30 PM”, “postitems”: [ { “itemCode”: “abc1″ }, { “itemCode”: “abc2″ }, { “itemCode”: “abc3″, “hours”: [{ “day”: “Monday-Thursday”, “time”: “2:00PM – 1:00AM” }, { “day”: “Friday-Sunday”, “time”: “10:00AM – 2:00AM” }] }, { “itemCode”: “abc4″ } ] } This issue can be resolved by the Power Query editor formula as given below. let #”source” = Json.Document(File.Contents(“d:pathfilename.json”)), #”tabled “= Table.FromRecords({source}), #”expandListField” = Table.ExpandListColumn(tabled, ” postitems “), #”expandRecField” = Table.ExpandRecordColumn(expandListField, ” postitems “, {” itemCode “, “hours”}, {” itemCode “, “hours”}), #”expandList2″ = Table.ExpandListColumn(expandRecField, “hours”), #”fieldForRec” = Table.AddColumn(expandList2,”Rec”, each if Value.Is([hours], type record) then [hours] else null, type record), #”fieldForList” = Table.AddColumn(fieldForRec, “List”, each if Value.Is([hours], type list) then [hours] else null, type list), #”expandList3″ = Table.ExpandListColumn(expandRecField2, “List”) #”Replaced Value” = Table.ReplaceValue(#”expandList3″, each [List], each if [List]=null then [Rec] else [List], Replacer.ReplaceValue, {“List”}), #”expandRecField2″ = Table.ExpandRecordColumn(#”Replaced Value”, “Rec”, {“day”, “time”}, {“day”, “time”}), in expandRecField2 The formula here creates a set of lists and records into separate columns. #”fieldForRec” = Table.AddColumn(expandList2,”Rec”, each if Value.Is([hours], type record) then [hours] else null, type record), #”fieldForList” = Table.AddColumn(fieldForRec, “List”, each if Value.Is([hours], type list) then [hours] else null, type list), Then expand the column with a list. #”expandList3″ = Table.ExpandListColumn(expandRecField2, “List”) It makes both the column with the same data type i.e., Records Now we merge both the columns by replacing the null values in the list column. #”Replaced Value” = Table.ReplaceValue(#”expandList3″, each [List], each if [List]=null then [Rec] else [List], Replacer.ReplaceValue, {“List”}), Now we only have a record that contains “Employee Name” in the column, and we can expand the data to be used. In figure: Column with an only record data type that can be further expanded and get Employee Name Conclusion: Here we transformed data for multiple types of values into single columns with PowerBI. Next Steps- Join us in a one-day workshop Dashboard in a Day (DIAD) and Paginated Reports in a Day (PRIAD) to understand and explore Power BI as a solution for business data collaboration.With the new year upon us, this could be the right time to consider this powerful business management solution. With the flexibility to start small and scale, and with the advantages as outlined such as lower costs, unification, and ease of use, why not? Also Know Why Microsoft Power BI is the leader in business analytics? Gain advantage of a phased approach for your next BI project. Learn How to embed a Power BI report into an application for your customers? Ankit Panchal Ankit Panchal is currently working as Senior Associate in Advaiya and has worked on Microsoft technologies for past 3.5 years and has been a SharePoint expert. Currently is working on Microsoft Power Platforms. Contact us by filling the form below for Power BI implementation and integration.
Driving digital transformation using Dynamics 365
Not so long ago, we had to explain what digital transformation is to our clients and prospects and what it can do for their business. But now, things have completely transformed; most business leaders have started embracing the digital innovation mindset. A recent study also shows that 91% of business leaders see digital transformation as a way of sparking innovation and finding efficiencies. In the wake of the COVID-19 pandemic, one thing is clear that businesses who had embraced the digital transformation or had started their journey towards it were able to maintain business continuity at a significant-level during the lockdown. Generally, digital transformation refers to the advancement that businesses are making toward digitizing everything they do — from managing customers, resources, operations, and finances to sharing information and gaining meaningful insights whenever and wherever they want. Everything that was once manual or paper-based can be digitized and therefore transformed with technology into something more cost-effective and efficient. Regardless of the industry or segment, digital transformation now is a business transformation mantra for every business, and Microsoft Dynamics 365 is a suite of intelligent business applications built specifically to enable organizations to drive digital transformation and evolve from reactive business decisions to proactive insights. Dynamics 365 applications remove the complexity of managing disparate customer relationship management (CRM) and enterprise resource management (ERP) systems. Digital transformation through Dynamics 365 is the integration of digital technology into all areas of a business, from marketing, sales, customer service, and field service, to human resources, supply chain, and finance. Although the benefits of Dynamics 365 are endless, I just want to highlight three key business benefits that help companies achieve tailored modern experiences that market demands. Grow at your own pace Dynamics 365 applications are designed to be deployed independently while seamlessly working together with existing Microsoft applications. So, you can easily start with apps that are the right fit for your business – and pay only for what you use. You can always scale-up/scale-down based on your growing business needs. Streamline business processes Dynamics 365 integrates with low-code solutions like Logic Apps, Power Apps, Power Automate, and Power Virtual Agents to extend the capabilities and solve business challenges through custom apps, workflows, and rich forms. The integration helps. create innovative solutions to increase productivity, simplify processes, and automate repetitive tasks. Make smarter decisions Microsoft Dynamics 365 easily connects with powerful analytics applications and tools, including PowerBI, Cortana Intelligence, and Azure Machine Learning, that help you transform your organization and gain insights that help you make smarter business decisions – better and faster. The COVID-19 has highlighted the importance of accelerating digital initiatives — not as an afterthought, but as a business imperative. We have worked with many companies across several industries and segments on their digital transformation journey and learned a lot along the way. From our experience, digital transformation does not fail due to lack of technology, but due to the fact that the technology is not implemented, integrated, and adopted properly. At Advaiya, we follow a structured process, phase-based approach, and industry-specific frameworks throughout the journey to make technologies work for you and help you make most of your investments. The journey begins with the discovery and envisioning phase, where we work with various stakeholders to understand the organization’s current state and identify potential areas of improvement to plan the future-state and roadmap. The discovery phase is then followed by design and development, implementation and deployment with successful adoption and training phases to deliver the value to our customers. We believe that breaking down the requirements into phases makes the process more manageable and transparent. Instead of having one large deliverable, the phased approach makes the team more productiveas the milestones are well defined, and the development process is more iterative with frequent interaction with the project stakeholders that allows smooth implementation. When all is said and done, we must be aware of the fact that the digital transformation does not automatically kick in when new technologies are brought into the enterprise and does not happen overnight. It is a continuous process to reimagine the overall business transformation based on changing business needs. If you’re excited to embark on digital transformation journey, don’t tackle it alone, we are there to help you! Digital Transformation Let’s start or accelerate your digital transformation journey with Advaiya