Excel Archives - Business Intelligence Info

Tag Archives: Excel

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

December 31, 2020 Big Data

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.

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.

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.

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.

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

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

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

Let’s block ads! (Why?)

#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″”> <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″”> <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

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

#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.