• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Tag Archives: Excel

You don’t code? Do machine learning straight from Microsoft Excel

December 31, 2020   Big Data

Transform 2021

Join us for the world’s leading event about accelerating enterprise transformation with AI and Data, for enterprise technology decision-makers, presented by the #1 publisher in AI and Data

Learn More


Machine learning and deep learning have become an important part of many applications we use every day. There are few domains that the fast expansion of machine learning hasn’t touched. Many businesses have thrived by developing the right strategy to integrate machine learning algorithms into their operations and processes. Others have lost ground to competitors after ignoring the undeniable advances in artificial intelligence.

But mastering machine learning is a difficult process. You need to start with a solid knowledge of linear algebra and calculus, master a programming language such as Python, and become proficient with data science and machine learning libraries such as Numpy, Scikit-learn, TensorFlow, and PyTorch.

And if you want to create machine learning systems that integrate and scale, you’ll have to learn cloud platforms such as Amazon AWS, Microsoft Azure, and Google Cloud.

Naturally, not everyone needs to become a machine learning engineer. But almost everyone who is running a business or organization that systematically collects and processes can benefit from some knowledge of data science and machine learning. Fortunately, there are several courses that provide a high-level overview of machine learning and deep learning without going too deep into math and coding.

But in my experience, a good understanding of data science and machine learning requires some hands-on experience with algorithms. In this regard, a very valuable and often-overlooked tool is Microsoft Excel.

To most people, MS Excel is a spreadsheet application that stores data in tabular format and performs very basic mathematical operations. But in reality, Excel is a powerful computation tool that can solve complicated problems. Excel also has many features that allow you to create machine learning models directly into your workbooks.

While I’ve been using Excel’s mathematical tools for years, I didn’t come to appreciate its use for learning and applying data science and machine learning until I picked up Learn Data Mining Through Excel: A Step-by-Step Approach for Understanding Machine Learning Methods by Hong Zhou.

Learn Data Mining Through Excel takes you through the basics of machine learning step by step and shows how you can implement many algorithms using basic Excel functions and a few of the application’s advanced tools.

While Excel will in no way replace Python machine learning, it is a great window to learn the basics of AI and solve many basic problems without writing a line of code.

Linear regression machine learning with Excel

Linear regression is a simple machine learning algorithm that has many uses for analyzing data and predicting outcomes. Linear regression is especially useful when your data is neatly arranged in tabular format. Excel has several features that enable you to create regression models from tabular data in your spreadsheets.

One of the most intuitive is the data chart tool, which is a powerful data visualization feature. For instance, the scatter plot chart displays the values of your data on a cartesian plane. But in addition to showing the distribution of your data, Excel’s chart tool can create a machine learning model that can predict the changes in the values of your data. The feature, called Trendline, creates a regression model from your data. You can set the trendline to one of several regression algorithms, including linear, polynomial, logarithmic, and exponential. You can also configure the chart to display the parameters of your machine learning model, which you can use to predict the outcome of new observations.

You can add several trendlines to the same chart. This makes it easy to quickly test and compare the performance of different machine learning models on your data.

 You don’t code? Do machine learning straight from Microsoft Excel

Above: Excel’s Trendline feature can create regression models from your data.

In addition to exploring the chart tool, Learn Data Mining Through Excel takes you through several other procedures that can help develop more advanced regression models. These include formulas such as LINEST and LINREG, which calculate the parameters of your machine learning models based on your training data.

The author also takes you through the step-by-step creation of linear regression models using Excel’s basic formulas such as SUM and SUMPRODUCT. This is a recurring theme in the book: You’ll see the mathematical formula of a machine learning model, learn the basic reasoning behind it, and create it step by step by combining values and formulas in several cells and cell arrays.

While this might not be the most efficient way to do production-level data science work, it is certainly a very good way to learn the workings of machine learning algorithms.

Other machine learning algorithms with Excel

Beyond regression models, you can use Excel for other machine learning algorithms. Learn Data Mining Through Excel provides a rich roster of supervised and unsupervised machine learning algorithms, including k-means clustering, k-nearest neighbor, naive Bayes classification, and decision trees.

The process can get a bit convoluted at times, but if you stay on track, the logic will easily fall in place. For instance, in the k-means clustering chapter, you’ll get to use a vast array of Excel formulas and features (INDEX, IF, AVERAGEIF, ADDRESS, and many others) across several worksheets to calculate cluster centers and refine them. This is not a very efficient way to do clustering, but you’ll be able to track and study your clusters as they become refined in every consecutive sheet. From an educational standpoint, the experience is very different from programming books where you provide a machine learning library function your data points and it outputs the clusters and their properties.

 You don’t code? Do machine learning straight from Microsoft Excel

Above: When doing k-means clustering on Excel, you can follow the refinement of your clusters on consecutive sheets.

In the decision tree chapter, you will go through the process calculating entropy and selecting features for each branch of your machine learning model. Again, the process is slow and manual, but seeing under the hood of the machine learning algorithm is a rewarding experience.

In many of the book’s chapters, you’ll use the Solver tool to minimize your loss function. This is where you’ll see the limits of Excel, because even a simple model with a dozen parameters can slow your computer down to a crawl, especially if your data sample is several hundred rows in size. But the Solver is an especially powerful tool when you want to fine-tune the parameters of your machine learning model.

 You don’t code? Do machine learning straight from Microsoft Excel

Above: Excel’s Solver tool fine-tunes the parameters of your model and minimizes loss functions.

Deep learning and natural language processing with Excel

Learn Data Mining Through Excel shows that Excel can even express advanced machine learning algorithms. There’s a chapter that delves into the meticulous creation of deep learning models. First, you’ll create a single layer artificial neural network with less than a dozen parameters. Then you’ll expand on the concept to create a deep learning model with hidden layers. The computation is very slow and inefficient, but it works, and the components are the same: cell values, formulas, and the powerful Solver tool.

 You don’t code? Do machine learning straight from Microsoft Excel

Above: Deep learning with Microsoft Excel gives you a view under the hood of how deep neural networks operate.

In the last chapter, you’ll create a rudimentary natural language processing (NLP) application, using Excel to create a sentiment analysis machine learning model. You’ll use formulas to create a “bag of words” model, preprocess and tokenize hotel reviews, and classify them based on the density of positive and negative keywords. In the process you’ll learn quite a bit about how contemporary AI deals with language and how much different it is from how we humans process written and spoken language.

Excel as a machine learning tool

Whether you’re making C-level decisions at your company, working in human resources, or managing supply chains and manufacturing facilities, a basic knowledge of machine learning will be important if you will be working with data scientists and AI people. Likewise, if you’re a reporter covering AI news or a PR agency working on behalf of a company that uses machine learning, writing about the technology without knowing how it works is a bad idea (I will write a separate post about the many awful AI pitches I receive every day). In my opinion, Learn Data Mining Through Excel is a smooth and quick read that will help you gain that important knowledge.

Beyond learning the basics, Excel can be a powerful addition to your repertoire of machine learning tools. While it’s not good for dealing with big data sets and complicated algorithms, it can help with the visualization and analysis of smaller batches of data. The results you obtain from a quick Excel mining can provide pertinent insights in choosing the right direction and machine learning algorithm to tackle the problem at hand.

Ben Dickson is a software engineer and the founder of TechTalks. He writes about technology, business, and politics.

This story originally appeared on Bdtechtalks.com. Copyright 2020

VentureBeat

VentureBeat’s mission is to be a digital townsquare for technical decision makers to gain knowledge about transformative technology and transact.

Our site delivers essential information on data technologies and strategies to guide you as you lead your organizations. We invite you to become a member of our community, to access:

  • up-to-date information on the subjects of interest to you,
  • our newsletters
  • gated thought-leader content and discounted access to our prized events, such as Transform
  • networking features, and more.

Become a member

Let’s block ads! (Why?)

Big Data – VentureBeat

Read More

Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

December 3, 2020   Self-Service BI

When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.

113020 2035 usehiddenme1 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

But the hidden fields cannot be seen if you browse the dataset in Excel.

113020 2035 usehiddenme2 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

But that does not mean that you cannot use the fields in Excel – and here is how you can do it.

Using VBA

You can use VBA by creating a macro

113020 2035 usehiddenme3 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

The code will add the field AddressLine1 from the DImReseller dimension as a Rowfield if the active cell contains a pivotable.

113020 2035 usehiddenme4 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table
Sub AddField()
    Dim pv As PivotTable
        Set pv = ActiveCell.PivotTable
        pv.CubeFields("[DimReseller].[AddressLine1]").Orientation = xlRowField
End Sub

If you want to add a measure/value to the pivotable you need to set change the Orientation property to xlDataFields

113020 2035 usehiddenme5 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

This means that we now have added two hidden fields from the dataset

113020 2035 usehiddenme6 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Add hidden measures using OLAP Tools

You can also add hidden measures using the OLAP Tools and MDX Calculated Measure

113020 2035 usehiddenme7 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Simply create a new calculated measure by referencing the hidden measure in the MDX

113020 2035 usehiddenme8 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

This will add a calculated Measure to the measure group you selected

113020 2035 usehiddenme9 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

And you can add that to your pivotable

113020 2035 usehiddenme10 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Referencing hidden items using CUBE functions

Notice that you can also reference the hidden measures using CUBE functions

113020 2035 usehiddenme11 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Simply specify the name of the measure as the member expression in this case as “[Measures].[Sales Profit]”

You can also refer to members from hidden fields using the CUBEMEMBER functions

113020 2035 usehiddenme12 Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

Hope this can help you too.

Power On!

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Read More

Free Workshop From Microsoft and P3: Modern Excel Analyst In A Day

December 1, 2020   Self-Service BI

A FREE Course By P3, In Partnership With Microsoft
on Tuesday December 8th, 2020 9am-5pm EST

Do you have Excel Analysts spending hours wrangling data week after week to provide updated reporting and analytics? Do you have Analysts starting to explore how Power BI can help but they’re in need of instructor-led training & hands-on workshop to gain experience? Are you unsure how Power Pivot, Power Query, Power BI, and Power Platform are all used together with O365, MS Teams, and SharePoint? If this sounds familiar, our team can help!

First come, first scheduled in this FREE event, and seating is limited to 50! Experience how Excel, together with Power BI, can empower your analysts to transform repetitive tasks into fully automated, action-driven solutions. Modernize and empower your organization while driving critical business impact. Did we mention that it’s a free workshop?

This workshop is perfect for Excel analysts, Power BI analysts, and data enthusiasts!

Click HERE to reserve your spot

On completion, attendees will:

  • Have Awareness of Power Query & Power Pivot In Excel
  • Learn how Power Query & Power Pivot In Excel can improve efficiency, provide reusable seamless solutions and transform existing manual reporting into actionable insights
  • Understand how Excel maps into Power BI Desktop
  • Identify opportunities to use Excel & Power BI together
  • Integrate Excel & Power BI solutions into Microsoft Teams & SharePoint

Share this:

Let’s block ads! (Why?)

P3

Read More

#PowerBI – External tool to connect Excel to the current PBIX file

July 28, 2020   Self-Service BI

In the July update of the Power BI Desktop we now can add external tools to the ribbon.

If you install the latest versions of Tabular Editor, DAX Studio and the ALM Toolkit these will be added as tools in the ribbon.

But you can also build and add your own tools.

David Eldersveld (link) has written an excellent series of blogposts about using Python as an external tool – link to part one – and this inspired me to give it a go as well.

The official documentation can be found here.

Short description of what an external tool really is

An external tool will point to an exe file and you can supply the call to the exe file with arguments including a reference to the %server% and %database%.

The information about the external tool needs to be stored in

C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

And name the file “<tool name>.pbitool.json”.

 #PowerBI – External tool to connect Excel to the current PBIX file

This will give me these buttons in my Power BI Desktop

 #PowerBI – External tool to connect Excel to the current PBIX file

My idea to an external tool

When I build models – I use Excel pivot tables to test and validate my measures and typically I would use DAX Studio to find the localhost port to setup a connection to the currently open PBIX file.

So, I thought it be nice just to click a button in PowerBI Desktop to open a new Excel workbook with a connection to the current model. That would save me a couple of clicks.

If I could create an ODC file when clicking on the button in Power BI and then open the ODC file (Excel is the default application to open these) my idea would work.

I have previously used Rui Romano’s (link) excellent PowerBI powershell tools – link to github and link his blogpost about analyse in Excel – so why not use PowerShell to do this.

Here is a guide to build your own version

Step 1 Create a powershell script

I created a powershell file called ConnectToExcel.ps1 and saved the file in local folder C:\Temp – you can save this where you want it stored. (Link to sample files last in this post)

The script is a modified version of Rui’s function Export-PBIDesktopODCConnection – thank you so much these.

Function
ET-PBIDesktopODCConnection

{

# modified the https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS.Tools/PowerBIPS.Tools.psm1

# the Function Export-PBIDesktopODCConnection

    [CmdletBinding()]

param

(

[Parameter(Mandatory =
$ false)]

        [string]

$ port,

[Parameter(Mandatory =
$ false)]

        [string]

$ path

)

$ port = $ port

$ odcXml= “<html xmlns:o=””urn:schemas-microsoft-com:office:office””xmlns=””http://www.w3.org/TR/REC-html40″”><head><meta http-equiv=Content-Type content=””text/x-ms-odc; charset=utf-8″”><meta name=ProgId content=ODC.Cube><meta name=SourceType content=OLEDB><meta name=Catalog content=164af183-2454-4f45-964a-c200f51bcd59><meta name=Table content=Model><title>PBIDesktop Model</title><xml id=docprops><o:DocumentProperties xmlns:o=””urn:schemas-microsoft-com:office:office”” xmlns=””http://www.w3.org/TR/REC-html40″”&gt; <o:Name>PBIDesktop Model</o:Name> </o:DocumentProperties></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc=””urn:schemas-microsoft-com:office:odc”” xmlns=””http://www.w3.org/TR/REC-html40″”&gt; <odc:Connection odc:Type=””OLEDB””>

<odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=$ port;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033</odc:ConnectionString>

<odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection></xml></head></html>”

#the location of the odc file to be opened

$ odcFile = “$ path\excelconnector.odc”

$ odcXml|Out-File $ odcFile -Force

# Create an Object Excel.Application using Com interface

$ objExcel=New-Object -ComObject Excel.Application

# Make Excel visible

$ objExcel.Visible = $ true

# Open the Excel file and save it in $ WorkBook

$ WorkBook = $ objExcel.Workbooks.Open($ odcFile)

}

write $ args[0]

ET-PBIDesktopODCConnection -port $ args[0] -path “C:\Temp”

The script contains a function that creates an ODC file where the Datasource and path of the ODC file is determined by to arguments in the function – port and path, The Script also opens Excel and then opens the file.

The scripts contain a

$ args[0]

This will in the end be the value localhost:xxxxx that will be provided when we click the External tool button in Power BI Desktop – and will make more sense after step 2

Notice that I have hardcoded the path where the ODC file will be stored to C:\Temp.

Step 2 Create a .pbitool.json file

The pbitool.json file is relatively simply

 #PowerBI – External tool to connect Excel to the current PBIX file

Name is the text that will appear in the ribbon.

Description is the tooltip that appears in Power BI Desktop according to the documentation – but it doesn’t work at the moment.

Path is the reference to the exe file you want to activate – and only the exe file.

Arguments is the arguments that you want to pass the exe file – and here we have the to built in references %server% and %database%. Arguments are optional so we could just start Excel or any other program if we wanted .

IconData is the icon that you want to appear in the ribbon – I found an icon via google and then used https://www.base64-image.de/ to convert it to the string.

In this tool we use the Powershell.exe file that can be called with arguments where we specify the script file that we want to be executed and we pass the extra arguments server and database as well – in my script I only use the %server% reference which will give me the server name and portnumber of the local instance.

It means that when the button is clicked in PowerBI Desktop it will execute

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe C:\temp\connetToExcel.ps1 localhost:xxxxx databasename

The localhost:xxxxxx can is the first argument provided and the value can then be referred to by using $ args[0].

The file must then be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools and in my case I called it OpenInExcel.pbitool.json.

Depending on your privileges on your computer you might be warned that you need administrative rights to save files in that location.

And if you save the script file elsewhere you need to modify the pbitool.json file.

Step 3 – Test it

Now we are ready to restart Power BI Desktop – and

 #PowerBI – External tool to connect Excel to the current PBIX file

And it does appear

Next – open a pbix file

This will open a Windows PowerShell window and write the server information

 #PowerBI – External tool to connect Excel to the current PBIX file

And in the background opens Excel and the ODC file – which results in a pivotable connected to the local instance.

 #PowerBI – External tool to connect Excel to the current PBIX file

With a connection to the localhost:52510

 #PowerBI – External tool to connect Excel to the current PBIX file

The files

You can download the files needed from here – https://github.com/donsvensen/erikspbiexcelconnector

Feedback

I think the use of PowerShell opens a lot of interesting scenarios for external tools and I look forward to see what other external tools that appear in the community.

Please let me know what you think and if you find it useful.

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Read More

1-Click app to export and email Excel Templates from Dynamics 365 CRM, something you have been looking for!

June 20, 2020   CRM News and Info

Dynamics 365 data is stored in form of records. These can be exported offline in form of Reports, Word or Excel templates. In order to export the reports, word and excel templates there is a complex procedure in Dynamics 365. To ensure an ease in exporting these templates we have introduced a productivity app Click2Export.

Click2Export is a 1-Click app to export Report, Word and Excel templates. This makes it easy for a CRM user to keep an offline record of the files after they have been exported. There is different procedure for exporting all these templates. In this blog, let’s explore how the exporting of Excel templates is done.

Excel template is exported for an entire view. For instance, say a user wants to export templates for an individual record, then they can use report or word templates. However, if they want to export an excel template, it has to be done for an entire view.

For any of these exported excel templates there are two actions applicable to these files. They are attach to an email and download for offline consumption. For the exported files these actions can be applied, however if the user exports Report or Word Templates other actions such as attach to a note or upload to SharePoint is also applicable.

Users can also schedule reports for excel templates. This means that at a regular interval the excel template will be exported and attached to a mail and auto-sent. This saves a lot of time of exporting the reports and sending them at different intervals. This eliminates the overhead that the team has to go through.

Therefore, it is quite easy to export these files in the click of a button. In case you want to export excel templates too, Click2Export is the right fit for you. For a free demo of the app mail at crm@inogic.com.

In order to explore the solution download it for a free trial of 15 days from our website or Microsoft AppSource.

Happy Exporting!

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Read More

#PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

April 25, 2020   Self-Service BI

Today I discovered a way to easily copy all queries (including the groups) from a PBIX file to Excel without entering the power query editor in Excel.

In my case I needed to copy the data returned by a query and find it some much easier to get the data using Excel instead of generating a csv file from a visual in Power BI Desktop and importing that into Excel.

My PBIX contains some queries to extract weather data from different stations in Sweden.

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

These are grouped to create a logic structure of the queries.

Select all queries and copy them.

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

Now open a blank workbook and click the Queries & connections in the Data tab in the ribbon

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

And the Queries & Connections panels opens

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

Now right click in the panel

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

And select Paste – and the queries, parameters and load settings for the queries are also included.

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

For the table you want to load you now need to specify whether you want to load to the table to the datamodel or to a worksheet – default will be worksheet.

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

If you collapse all your groups in the panel and select all of them you can also copy them

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

And paste them into the Power Query editor in the Power BI Desktop

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

And after paste

 #PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa

Hope you this can help you too and stay Queryious

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Read More

Microsoft Partners and CSPs Need Automated Billing Systems – Why Excel Isn’t Enough

November 15, 2019   Microsoft Dynamics CRM

If you’re in the business of generating recurring revenue like Microsoft CSP Partners and using Excel to handle your customer’s subscriptions and billing, you’re not alone. Dynamics 365 can easily be used to manage your sales and service, and with Work 365 you can manage recurring revenue and Billing automation for subscription sales. While Partner Center and Distributor portals are available, most Service providers still use spreadsheets to log and record the changes that are being made to subscriptions and services. These changes along with a combination of all the different invoices are used to invoice and bill a customer.

This process can take hours or even days for teams of sales and accounting members to manually go through customer accounts and match up customer data in Dynamics CRM with the appropriate subscription changes in order to manually create and send out invoices. Most importantly this process is not scalable.

automated billing 625x192 Microsoft Partners and CSPs Need Automated Billing Systems – Why Excel Isn’t Enough

Automating this process can save a service provider hundreds of employee hours that can be used to focus on value creation tasks around customer service and sales to grow the business. Billing automation solutions like Work 365, that are built on Dynamics 365 allow Microsoft Partners to grow their recurring revenue with these great features around billing automation:

  • Bundling products and services into custom packages
  • Automatically Creating invoices by calculating prorations, refunds, and renewals
  • Automatically Sending and Collecting payment for invoices through
  • Calculating monthly Sales commissions and incentives
  • Integrating with accounting systems
  • Enabling Self-Service scenarios for customers to directly provision and manage their services

Work 365 is built on Dynamics 365 and has bi-synchronization with Partner Center, all subscription changes, billing, and payment status, and invoicing can be done directly from Work 365 with a click of a button, saving Partners and CSPS time and using multiple systems to keep their records in check.

Watch our webinars to see how Work 365 can help Indirect and Direct CSPs automate and reconcile billing and invoicing.

ismail nalwala iotap 150x150 Microsoft Partners and CSPs Need Automated Billing Systems – Why Excel Isn’t Enough

By Ismail Nalwala

I am a Dynamics 365 enthusiast. I enjoy building systems and working with cross-functional teams to solve problems and build processes from lead generation to cash collection. Work 365 is a global developer of the Billing Automation and subscription application for Dynamics. Helping companies to streamline business processes and scale their recurring revenue.

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Read More

Sales Efficiency: 5 Reasons CPQ for Microsoft Dynamics Surpasses Excel

July 25, 2019   CRM News and Info

Sales Efficiency Reasons CPQ for Microsoft Dynamics Surpasses Excel Sales Efficiency: 5 Reasons CPQ for Microsoft Dynamics Surpasses Excel

There is a common problem in many organizations today, and it arises from the use of spreadsheets to organize and manage business processes.

Excel has served companies well for more than 30 years, and it continues to be useful for many purposes, including organizational management and finances. But, there are limitations to its use in sales operations and its ability to scale.

I encourage you to consider the limitations of Excel in product configuration, pricing, and quote generation. It may be time for your company to ditch Excel and opt for CPQ integrated with Microsoft Dynamics.

CPQ (configure-price-quote software) enables organizations to configure their product and service offerings and automate the generation of quotes and proposals. A CPQ can be configured using pricing models, discounting, and product dependencies and includes contingencies for approvals. The software tracks receipt of, and engagement with, the document. E-signature is also built into many CPQ solutions.

5 Reasons to Transition from Excel to CPQ Integrated with Microsoft Dynamics

Excel may be a good solution for small businesses and simple operations. However, it becomes cumbersome, time-consuming, error-prone, and difficult to scale as your business grows.

For organizations performing complex product and pricing configurations and generating sales quotes, Excel is inefficient and inadequate. Spreadsheets become more complicated, bulkier, and downright unwieldy. Over time, manual configuration, pricing, and quoting become more time-consuming and inefficient.

1.  You’re at the mercy of your “Excel guru”

In many sales organizations, there’s a single individual who creates and maintains an Excel spreadsheet. This is a necessary evil for version control and to reduce the risk of errors. However, it puts your sales operations in a difficult position because there’s only one person who can effectively manage it. And, this spreadsheet becomes exponentially more complex over time as more products are added and updates are made.

CPQ integrated with Microsoft Dynamics empowers sales team members across multiple departments to make changes seamlessly, and the information is updated in real-time. You’ll reduce errors and time spent on administrative tasks like manually updating spreadsheets.

2.  Error-prone spreadsheets derail deals

The potential for human error is inherent in the manual quoting process. Even a pro sales rep can make mistakes that result in an inaccurate quote – and then the sale comes to a screeching halt.

Using outdated spreadsheets, selecting the wrong options for a product/service, or applying an outdated discount, can derail deals. CPQ minimizes errors significantly and ensures the highest level of accuracy in proposals. Also, using a CPQ with a sales playbook places guardrails around deals, so they stay on track, are accurate and ensure products offered and quotes meet the needs of buyers.

3.  Quote approval bottlenecks

Using Excel to generate quotes is a slow, inefficient process. Your sales reps may need to consult different pricing sheets (which may or may not be up to date). Discount requests that require additional approval slow the process further. Once quotes are sent for approval, the process is often sluggish, which reduces the number of proposals generated.

CPQ automates the quote generation and approval process, liberating your sales team from manual quoting and approvals. Discounts are configured within the software so that you and your sales managers can quickly approve quotes and keep deals moving forward.

Aberdeen Group found CPQ implementation results in an increase in the number of proposals generated by sales reps each month of nearly 50 percent.

4.  Security threats 

An often overlooked, but immensely consequential, issue with using Excel for quotes and proposals results from their vulnerability to security-related complications. Security is a valid concern when multiple people have access to sensitive information on an Excel spreadsheet; it can wind up in the hands of competitors or other unauthorized third parties.

A CPQ enables administrators to regulate who has access to data and protect proprietary information and prevent security threats.

5.  Lengthy sales cycles

Excel has automated functions, yet it still demands tremendous time and manual effort to use. Sales reps must search through sales pricing lists, browse inventory, convert quotes to orders, and more. These tasks are time-consuming and slow down the sales cycle.

A CPQ solution rises above the limitations of Excel configurators and speeds up the sales cycle dramatically. Sales teams can access accurate information in real-time, hasten collaboration and approvals, and deliver accurate quotes and proposals with less time and effort.

Aberdeen Group’s research indicates that the average sales cycle of CPQ users is 3.42 months, while the average sales cycle of non-CPQ users is 4.68 months.

CPQ Helps Sales Teams Succeed 7.19 625x625 Sales Efficiency: 5 Reasons CPQ for Microsoft Dynamics Surpasses Excel

The Impact of CPQ on Business Outcomes

CPQ Replaces Excel

Still using Excel? It’s time to streamline your sales operations and simplify your sales reps’ lives with CPQ.

DealHub CPQ integrates seamlessly with Microsoft Dynamics 365. With a single sign-on, your sales reps can create quotes using a dynamic CPQ solution that reduces errors, produces quotes quickly, and streamlines approvals. Our CPQ enhances the buyer experience by sending quotes and relevant sales content in a personalized DealRoom which aids stakeholders in decision-making.

Want more information on transitioning from Excel to CPQ? Check out five of the most troublesome Excel Configurator Limitations and how CPQ overcomes them.

DealHub Sales Insights for Microsoft Dynamics GIF Sales Efficiency: 5 Reasons CPQ for Microsoft Dynamics Surpasses Excel

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Read More

#PowerQuery – Replicate doing an Excel VLOOKUP in M

March 3, 2019   Self-Service BI

Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly replicate some of the VLOOKUP functionality and make an even more flexible lookup function.

Now the example data is like this

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

In Excel we would lookup the price for at specific productkey by using this formula

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

– in this case ProductKey 1 with a price of 100.

In order to replicate this in Power Query we can use the function List.PositionOf

So I add a new blank query

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

And then use the function List.PositionOf – with the following arguments

List – Is the column ProductKey from my lookuptable Products – refer to like this Products[ProductKey]

Value – Is the value to look in this case the value 1

Occurrence – Is set to 0 to only return one value

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

This will return the position of the value in the list – sort of like using the MATCH function in Excel

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

Now to return the price – we can use this result to lookup the price like this

= Products[Price]{List.PositionOf(Products[ProductKey], 1, 0)}

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

And we get 100 returned which is the price of productkey 1.

The formula is structured like this

=NameOfTheTable[NameOfTheColumnToReturnTheValueOf]{PositionReturnedByListPositionOf}

But we why not change it into a function in PowerQuery so we use the function on all rows in a table or on any table.

The function can be created like this

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

The code

(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
// lookupTable= Products,
// lookupColumnName = "ProductKey",
// returnColumnValue = "Price",
// lookupValue = 1,
 colLookup = Table.Column(lookupTable, lookupColumnName),
 colToReturn = Table.Column(lookupTable, returnColumnValue),
 lookup = List.PositionOf(colLookup, lookupValue, 0),
 Result = if lookup >=0 then colToReturn{lookup} else "Not found"
in
 Result

The function takes 4 arguments –

lookupValue – The value to find – can be any type

lookupTable – The Table/Query to lookup in

lookupColumnName – The name of the column to lookup the value in

returnColumnValue – The name of the column from the table to return

The colLookup is a variable that uses the function Table.Column to return a list of values in the lookup column.

The colToReturn is a variable that uses the function Table.Column to return a list of values from the values you want to return column.

The lookup variable uses the List.PositionOf to find the position/index of the search value in the lookup column.

Result will use an if statement to test whether a position is found and if so returns the value at the position in the colToReturn list – other wise returns the text “Not Found”.

After this we can use the function in other tables to lookup the for instance the Product price like this – by added a invoke Custom Function Column

 #PowerQuery – Replicate doing an Excel VLOOKUP in M

OBS – I haven’t tried this on a large table so be aware of any performance issues.

Hope you find this useful – and happy Querying

Here is a link to an example file – Link

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Read More

Excel announces new data visualization capabilities with Power BI custom visuals

May 7, 2018   Self-Service BI

Custom visuals have become a key part of Power BI allowing users to tap into hundreds of different visualizations. It is hard to believe that a little over two years ago we announced the availability of the first batch of custom visuals in Power BI Desktop and the Power BI Service. But custom visuals go far beyond the world of Power BI users. Today, as part of Build, we announced that Power BI Custom Visuals will be rolling out in Preview to Office 365 subscribers enrolled in the Office Insiders program soon, extending Excel charting capabilities and more than doubling the data visualization options for the most widely used data analytics tool in the world.

2051210e e17b 4320 b2bb cb4bbf391563 Excel announces new data visualization capabilities with Power BI custom visuals

Developers around the world are able to extend Power BI through visuals using the Power BI developer tools, the Power BI Visuals repo and standard Open Source technologies, like JavaScript and D3. This highlights Power BI’s commitment to user driven innovation and listening to feedback from our users and community.

Amir Netz, Technical Fellow and Chief Architect of Power BI, said in 2015, “I believe this one new capability [custom visuals] trumps all of the features released … Why? Because with custom visuals we are simply blowing past every limit of what is possible in data visualization and business intelligence.” As of today, more than 150 visuals are available in AppSource ranging from Infographics to R powered visuals that do not require R code at all!

0482a65b e398 40c9 83c4 1bf88b60bedc Excel announces new data visualization capabilities with Power BI custom visuals

Developers will now be able to reach millions of users with their own custom visuals while current Power BI developers will be able to reach a wider audience.

Here are some of the benefits that custom visuals will bring to Excel users and developers:

  • Insertion Experience side-by-side with existing charts

  • Tailored and Familiar Store

  • Intuitive and Familiar UI Controls within Excel

  • Enterprise Ready support for in house organizational visuals

  • Develop once and make available to users on both Excel and Power BI

f7c3d80c 19b6 4184 90d7 7c53241e6e2b Excel announces new data visualization capabilities with Power BI custom visuals

Finally, this announcement reinforces the benefits that Excel and Power BI users get from using both tools together in the context of Office 365.

Learn more about this announcement in the Office Dev Center blog.

Resources

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Read More
« Older posts
  • Recent Posts

    • Aurora partners with Paccar to develop driverless trucks
    • “Without Data, Nothing” — Building Apps That Last With Data
    • SO MUCH FOR GLOBAL WARMING, EH?
    • Important Changes to Microsoft Dynamics 365 Field Service Mobile App
    • Syncing Dynamics 365 User Permissions with SharePoint
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited