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.
Don’t just survive, grow through the crises with Power BI
Introduction Any business that generates essential customer and business data will find the use of Power BI relevant. Today, data is easily accessible to most of the companies, but we still cannot figure out how to optimally use all the data available to us. Our companies would perform much better if we could use the data optimally to make our decisions. Disruption by COVID-19 COVID-19 has changed how the businesses saw themselves, their goals, and the way they operate today. It is challenging for enterprises to restart post-COVID scenarios as the supply chains are disrupted, and the consumers’ as well as workforces’ behaviors, have changed. However, COVID-19 does not seem to be slowing down and may stick around much longer than we anticipated. This has led to increased uncertainty in the business environment. According to a recent survey by marketing week, as much as 68% of the marketing leaders feel that uncertainty is the major challenge for businesses. PwC experts’ article states that, in times of uncertainty and crisis, the biggest worry for business leaders is to gather information quickly. The primary question for them is, “How will data flow during this crisis?” Understanding the need Imagine you had a successful hospital before the COVID-19 outbreak. The sudden outbreak has everyone on their heels. During this panic state, you know you may have to serve an influx of patients. As a businessperson, you know, the supply of various essentials will get disrupted. It would help if you decided on the sudden changes in the inventory of medicines and assets like ventilators etc. You need information on the approximate number of patients you will be getting, and the specific medication you will need to treat these patients. The decisions taken by you now will impact the business as well as the life of your patients. Solving the problem with Power BI In such a scenario, the visualization of your customer demographics and the supply chain of essential medicines would make a vital impact on your decision. Now, imagine this was a case for all your branches across the world. You would want real-time data of all the users to choose all the hospitals. You might want to make a country-specific choice of stocking the medicines according to the number of cases in the country from your headquarters, and Power BI provides you with a facility to do that. Power BI would give you real-time dashboards of the number of patients, the diverse needs of these patients, country-specific data, and the stocks of the medicines. Benefits of Power BI COVID-19 has disrupted supply chains of businesses, and the workforce must work remotely, which may affect their productivity. Power BI provides real-time dashboards for all kinds of data generated by the company. It provides you with end to end visibility over the supply chain, the current inventory levels, anticipated resource requirements, and the approximate lead time to get the required products. Workforce productivity may also take a hit during these crisis times. Power BI provides you with a platform to keep track of employee productivity and motivation levels and provide valuable insights into employee behavior changes. It helps you compare the outcomes achieved through a process pre-COVID and post-COVID. Business leaders can use this data to motivate employees and take care of their needs. Power BI allows you to integrate the data from various sources like Excel, SQL Server, etc. on the cloud to provide insights for your business and help you in decision-making on multiple fronts. Availability on the cloud makes it easily accessible on a variety of devices like iOS, desktop, etc. It allows you to create live dashboards that provide you with a real-time picture of our business needs. It helps you develop customer segments, customer preferences, take inventory management decisions, and keep track of business opportunities in your industry landscape. The Advaiya advantage With Advaiya, you can develop your dashboards for the specific needs of your business. You can look at a few live dashboards using the following link. https://advaiya.com/live-power-bi-reports/ We can provide your business with customized dashboards that represent all the relevant areas for your business. We can help you understand and analyze the data on these dashboards and provide aid with the AI predictive modeling capabilities of the software to increase the efficiency of your business processes. Step by step process followed by Advaiya: We will figure out the business need of your business. Our experts can build on the previous data sources and infrastructure available in the organization. We will design a customized dashboard for your business. We will link your data sources to your dashboards for a real-time flow. We will train your employees to understand the business insights from predictive modeling. Decision making Are you ready to experience the business growth powered by Power BI?