Tag Archives: Excel
1-Click app to export and email Excel Templates from Dynamics 365 CRM, something you have been looking for!
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 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 or
Happy Exporting!
#PowerQuery – Easily Copy all queries from a PBIX to Excel and vice versa
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.
These are grouped to create a logic structure of the queries.
Select all queries and copy them.
Now open a blank workbook and click the Queries & connections in the Data tab in the ribbon
And the Queries & Connections panels opens
Now right click in the panel
And select Paste – and the queries, parameters and load settings for the queries are also included.
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.
If you collapse all your groups in the panel and select all of them you can also copy them
And paste them into the Power Query editor in the Power BI Desktop
And after paste
Hope you this can help you too and stay Queryious
Microsoft Partners and CSPs Need Automated Billing Systems – Why Excel Isn’t Enough
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.
Automating this process can save a 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.
to see how Work 365 can help Indirect and Direct CSPs automate and reconcile billing and invoicing.
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 and subscription application for Dynamics. Helping companies to streamline business processes and scale their recurring revenue.
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 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.
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 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.
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 .
A CPQ enables administrators to regulate who has access to data and protect proprietary information and prevent security threats.
5. Lengthy sales cycles
, 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.
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.
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.
. 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 .
#PowerQuery – Replicate doing an Excel VLOOKUP in M
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
In Excel we would lookup the price for at specific productkey by using this formula
– 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
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
This will return the position of the value in the list – sort of like using the MATCH function in Excel
Now to return the price – we can use this result to lookup the price like this
= Products[Price]{List.PositionOf(Products[ProductKey], 1, 0)}
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
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
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
An Oldie but Goodie – PowerPivot In Excel: Existing Data Connections
With the Power BI craze that kicked into high gear in 2017, it’s less common to be working in PowerPivot in Excel. However, during one of my recent remote consulting calls, one of our many clients needed some help with a solution created in PowerPivot in Excel.
And I often find if ONE person needs help or has a particular question about how to create a solution then it’s possible MANY other people might have a similar question or solution need. As such, that is the inspiration for today’s post!
I also like this solution idea that I’m about to share because it showcases a use case for why someone might prefer a PowerPivot In Excel solution over Power BI Desktop solution.
You can download the excel workbook to follow along with here.
Use Case
Let’s say you have…
- a DATA table called ‘TheData’ with [Company], [Department], [Employee ID], [Date], [Amount]
- a LOOKUP table called ‘Employee’ with [Employee ID] and [Employee Name]
You want to be able to create a Pivot Table report with the following information from ‘Employee,’ ‘TheData’ and [Employee Comments] …
- [Employee Name], [Total Data Rows], [Total Amount] AND [Employee Comments]
Requirements
- The source data comes from a SQL database or some other system source that you do not have access to edit
- The list of [Employee ID] and [Employee Name] must update dynamically – to include and new Employees or Employee changes
- The [Comments] will be manually entered and maintained in the Excel Workbook Solution File
Solution
The solution will use Data > Existing Connections > Tables to load the results from the ‘Employee’ table in the Data Model to a worksheet where the [Comments] column can be added, edited and loaded back into the Data Model.
Solution Steps
1.) Connect to the ‘TheData’ & ‘Employee’ data sources using Power Query
2.) Choose Home > Close and Load… Only Create Connection and Add this to the Data Model
3.) Create a Relationship from the MANY (*) side ‘TheData’[Employee ID] to the ONE (1) side ‘Employee’[Employee ID]
4.) Then, in a new worksheet, select Data > Existing Connections
Now, here’s the trick…
5.) From the Existing Connections choose the Tables tab and Show: Connection Tables, pick ‘Query – Employee’ and select Open
6.) Choose the Table, New Worksheet then select OK
7.) This will load the data from the ‘Employee’ table in the data model into a new worksheet
8.) Select Design from the menu and give the table a new name ‘ToTable_Employee’
9.) Now let’s add a column called [Comments] to the ‘ToTable_Employee’ table
10.) Select Data > From Table/Range to bring this new dynamic table into Power Query – let’s call it ‘Comments’
11.) Choose Home > Close and Load… Only Create Connection and Add this data to the Data Model
12.) The ‘Comments’ table loads to the data model, then create the Relationship from the MANY (*) side ‘Comments’[Employee ID] to the ONE (1) side ‘Employee’[Employee ID]
13.) Lastly, we can create the PivotTable report!
Now, any time a user enters comments on the worksheet, select Refresh on just the ‘Comments’ query in your Data > Queries & Connections pane, and the comments will be added to the report worksheet. And when you add new Employees or change Employees in the source data, a Data > Refresh All will update the ‘Table_Employee’ information, and you can enter the comments.
Conclusion
PowerPivot In Excel workbooks can be a great solution when you want to easily allow an end user to inject information into a data model report or dashboard!
So, in staying committed to PUG memes for 2019 – I thought this one was entirely appropriate for two reasons…
Reason #1
PowerPivot In Excel is indeed an OLD friend, now that Power BI Desktop has taken center stage! But there ARE many other reasons that a PowerPivot in Excel solution is just what you need.
Reason #2
For our northeastern friends experiencing the Winter 2019 polar vortex, sending you WARM sunshine vibes.
Happy Power Pivoting in Excel 🙂
No one knows the Power BI ecosystem better than the folks who started the whole thing – us.
Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.
* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.
A different use for Power BI: Self-service refreshes and extracts to Excel while managing security in Power BI
Have you ever needed to get data out to an internal customer quickly to meet an urgent business need, in an easy to access, updateable format?
Of course!
There are lots of ways to do this, and I’ve tried various routes at different times. Some were even successful, but I’ve never been entirely satisfied with meeting the combined requirements of security, control, and automation as well as customer satisfaction.
If I get the security aspect wrong, am I inadvertently giving the wrong person access to my database and the data it holds? If the control isn’t tight enough, are they going to be able to make changes or slow the performance of a system? To achieve sufficient control and security, am I sacrificing my ability to automate the data extract, leaving myself with a regular manual task to perform. If I tighten up all of these things, is the end result usable…or even recognizable…to the customer?
I know that in theory, an urgent task like this shouldn’t be repeatable without becoming a planned change or development, but think about the amount of time it takes to convert a request to a solution in production. Can I tell my Director that the requested data isn’t available on a regular basis until we’ve been through project management/ITIL/change control/any other relevant control systems? Then add in the development time needed, assuming my workload is already full to capacity.
This isn’t a show-stopping Problem-with-a-capital-P, it is more of an irritation, but it can be a recurring and persistent one. I’ve become more aware of it recently as I am in the middle of implementing a data warehouse to feed Power BI reports. Our team is combining data from different sources for the first time and making much more sophisticated information available, all of which takes time. In the meantime, the business has become aware of our progress, and they want to be included – every team needs something immediately.
While trying to meet an urgent need recently, it occurred to me that Power BI offers a simple solution. I can draw data from my new data warehouse, use views to combine the different sources, add filter criteria by entering a SQL query in the Power BI data source and present a simple data table for the end user. The resulting report can be published as a Power BI App, and the underlying dataset refreshed to a schedule.
My user then has straight-forward access to the data they want, in a recognizable format, refreshed as agreed. They can easily export to Excel for further analysis, and so they are happy. Security is set up during the App Workspace creation and the App publication, so as long as I add a member of my immediate team to the App Workspace Admin role, the whole process can easily be supported by someone other than me if I’m not around. The user can see their data and export it but does not have a direct connection to the data warehouse. Once the interim solution is in place, it doesn’t need my intervention.
There is a risk that these short-term solutions will never be replaced with properly scoped reports, but the plan is that all such solutions will be reviewed, as part of our on-going BI project. There are a few cases where extracting data in this way may even be the answer – an operations manager isn’t just looking for “how many of our properties need a survey within the next 60 days”, they need to know “which properties need a survey within the next 60 days”.
It is a simple solution to a problem that isn’t a big deal, but one I have found satisfying.
How do I do it?
Firstly, get the right data into the data warehouse (SQL Server in Azure in this instance) and create a view over it. The view should include enough data (columns and rows) to cover all related requests if possible. To demonstrate, I’m using a football World Cup data source I set up for a team sweepstake.
Create a new Power BI report with a SQL Server data source. In this example, the underlying tables and the view include all teams, whether or not they qualified. Those who qualified for the tournament have been assigned to a Group, so to restrict the view for this specific report I’m using a SQL statement in my data source.
Once the data is loaded into Power BI, I set up a very simple table which shows the requested details. As the point of the report isn’t a presentation, I’m not spending time on layout, format, labels or titles. As long as the table makes sense to the person or people making the request, it is good enough.
Now I save the report and publish it to a Power BI app workspace. I’m using new app workspaces, which are currently in preview, as they don’t create an entire Office365 group.
I set up a refresh schedule, if required, using an on-prem data gateway and add at least one other team member to the app workspace as with admin permissions.
Now the report is ready to publish as an app, giving the original requestor access to the app along the way.
When the user opens the report, they can see the data on the screen to check the content, then using the ellipsis on the corner of the table, export to Excel.
More Options
The user cannot edit the report, download the report file to see the SQL Server connection or access the original data, and no-one else can see it unless given permission.
My internal customer is happy as they can easily get to the data they need. My boss is happy because this is quick and easy to set up and frees up my time for the long-term projects he’d prefer me to focus on. The wider business need is satisfied until a more formal solution is available. And I’m happy to tick all of those boxes and be able to focus on the interesting, challenging big picture work I enjoy.
We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”
In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂
* – unless, of course, you have two turntables and a microphone. We hear a lot of things are also located there.
Excel announces new data visualization capabilities with Power BI custom visuals
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.
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!
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
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
Jan 23rd The Powerful Interactions between Excel and Power BI by Tristan Malherbe
One of the most popular presenters from the Power BI World Tour will be presenting this weeks webinar!
The Powerful Interactions between Excel & Power BI by Tristan Malherbe
Abstract
We will see all the interactions between Excel and Power BI (Power BI in Excel, Excel connector, Power BI Publisher for Excel, Analyse in Excel, Export to Excel, push Data model from Excel to Power BI Service) with practical examples and relevant scenarios.
When: 1/23/2018 10AM PST
Where: https://www.youtube.com/watch?v=sM8xkNyZaTU
About the presenter
Tristan Malherbe, a Power Microsoft MVP, a renowned Business Intelligence Consultant at AZEO and President of the Power BI User Group in France. As a PUG leader for Power BI in France, Tristan’s passion for the product comes from his interest in solving problems and creating user-friendly applications to support decision making, to analyse or visualise data.
Why You Need to Move Beyond Excel and What You Might be Jeopardizing If You Don’t
Let me start by saying Excel is an incredibly useful tool. It revolutionized accounting in the 1980s and handles projects from simple personal budgeting to any number of complex processes for businesses. But Excel has limitations—and it’s important to know when and where other tools should be used instead. Case in point? Business analytics.
Excel’s limitations are exactly why executives in major companies across a number of industries are opting for tools created specifically for business analytics and data visualization. Business can’t afford to waste precious resources importing, exporting, editing, and tracking spreadsheets rather than analyzing and making decisions based on the insights provided by data.
Excel operates separately from other systems, proliferates duplication and therefore errors, lacks live data, and is just beginning to allow collaboration. There are also limitations in the amount of data that can be put into a single document, and while Microsoft boasts Excel’s ability to handle millions of rows of data, it doesn’t accommodate the depth and complexity of data that many businesses have today.
Informing your business and making decisions using incomplete, error prone, or old data can be a huge disservice to your company. So while currently no business analytics tool can completely replace Excel, it is important to realize its limitations and how you may be stifling your business by not using true BI and data visualization tools for analytics.
These tools, like TIBCO Spotfire®, connect with your existing data sources in any enterprise or department, track changes, foster collaboration, and allow users to aggregate, analyze, and report business data in one place. Rather than spending time weeding through old data, you can rely on live data that better enables business decisions. By using Spotfire, you open a world of new possibilities, including ways to improve operational efficiency and increase revenue.
But that’s not all. Spotfire is filled with easy-to-use features that help spot trends and outliers, perform geoanalysis, and recommend visualizations—all while making governance and legal compliance a breeze.
It’s time to move beyond Excel to a tool as sophisticated as your data.
In fact, we have a number of companies who use Spotfire for finance. Check out some of the customer stories here.