Excel + blogs from Wise Owl (page 1 of 7)

Showing blogs 1-20 (out of 127)

Some ideas for how to map data in Power BI Desktop

Posted by Andy Brown on 08 June 2017

Anyone who has tried to get a meaningful non-US map out of Power BI Desktop will know that it's often not straightforward! This blog shows you how to overcome some of the issues, including geocoding data, getting latitude and longitude settings and changing cross filter settings in relationships.

Tags:   Power BI Desktop | Maps

A simple way to move a measure from one table to another

Posted by Andy Brown on 08 June 2017

You can't drag and drop measures between tables, but there is an easy way to move them in Power BI Desktop, as this blog shows

Three different ways to find the differences between two Excel worksheets

Posted by Andy Brown on 01 June 2017

There are a few ways to find the differences between two Excel worksheets. This blog considers 3 methods (using Excel formulae, using Query Editor and using the Inquire add-in).

The most important changes made for the May 2017 Power BI Desktop update

Posted by Andy Brown on 17 May 2017

The May 2017 Power BI Update brings in a couple of nice new query transforms and some new chart features, but the big changes are still in preview mode.

A list of the updates made in the April 2017 update to Power BI Desktop

Posted by Andy Brown on 28 April 2017

This update includes some nice new features - for example, creating columns by example and changing chart axis titles. Read this blog for a full list!

The main new features in the March 2017 update of Power BI Desktop

Posted by Andy Brown on 24 March 2017

This is a small update, although the new matrix tool which is currently in preview mode will be a big change.

Combine all the files in a folder into a single table using Power Query

Posted by Andy Brown on 21 February 2017

Sure, you can use Power Query (or Get & Analyze, as it's now called) to load and transform data, but did you know you can loop over all of the files in a folder, combining their data into a single set of results? This blog explains how to do this for CSV files, and gives a lead for how to proceed with Excel ones.

The new functions in Excel 2016, including IFS, SWITCH and TEXTJOIN

Posted by Andy Brown on 18 February 2017

Excel 2016 has a few interesting new functions! TEXTJOIN allows you to concatenate all the cells in a range, and IFS and SWITCH provide other ways to make choices.

Tags:   Excel | Formulae

Updates made in February 2017 to Power BI Desktop.

Posted by Andy Brown on 16 February 2017

A summary of the most important changes for Power BI Desktop users in the February 2017 update.

Power BI Desktop - January 2017 update

Posted by Andy Brown on 08 February 2017

A summary of the (small) changes in Microsoft's January 2017 Power BI Desktop update.

Want to be get at the file path for an Excel workbook easily? Here's how!

Posted by Andy Brown on 26 January 2017

This short blog explains how you can customise your quick access toolbar to display the path of the file that you are currently working on.

Tags:   Excel | General

A summary of the Power BI Desktop Updates going back to November 2016

Posted by Andy Brown on 19 December 2016

This blog differs from the Microsoft update summary in two ways: it omits preview and obscure changes, but goes into more detail (and gives practical examples) on mainstream updates.

The main new features for BI workers in the November 2016 Power BI update

Posted by Andy Brown on 19 December 2016

A summary of the new features in the November 2016 Power BI Update, omitting things which are still only in preview.

How to create running totals or cumulative figures in Power BI Desktop

Posted by Andy Brown on 19 December 2016

There are a host of date-based functions in Power BI Desktop for producing cumulative figures, but how do you get running totals for non-date columns? This blog explains!

Passing parameters to SQL stored procedures in Power BI Desktop

Posted by Andy Brown on 30 November 2016

This blog summarises how to run SQL statements in a Power BI Desktop query, using dynamic SQL to pass in parameter values

How to pick out the first and last dates for a given group using DAX

Posted by Andy Brown on 06 October 2016

It's often challenging working out how to use DAX to solve specific problems. This blog shows how to produce a pivot table showing the first and last instances of any category.

Tags:   Excel | PowerPivot    |    Power BI - Excel 2013 | PowerPivot

How to get Excel to read out cell values on calculation

Posted by Andy Brown on 26 September 2016

A short blog explaining how you can get Excel to read out values in cells every time that you recalculate a worksheet.

Tags:   Writing VBA | Other    |    VBA macros | Working with ranges

Which Power BI course should you take - Excel Power BI or Power BI Desktop?

Posted by Andy Brown on 22 July 2016

Microsoft are pursuing two separate BI lines at the moment: one for the Power BI tools which run within Excel, and another for the standalone application called Desktop Power BI. This blog attempts to explain the differences between two applications with such confusingly similar names!

Tags:   Excel | PowerPivot    |    Power BI - Excel 2013 | General    |    Power BI Desktop | General

Creating an exchange rate slicer in PowerPivot

Posted by Andy Brown on 11 March 2016

A way to create a slicer in PowerPivot to choose which exchange rate to use (ie for which currency to show results).

How PowerPivot differs from SSAS Tabular, and using it to prototype data models

Posted by Andy Brown on 18 February 2016

PowerPivot is virtually identical to SSAS Tabular underneatht the glossy exterior, but it has got a few extra features explained in this blog (which also shows how to import PowerPivot data models into Analysis Services, and why you might want to do this).

This page has 1 thread Add post
04 May 17 at 18:18

Hello,

I'm having troubles in achieving something on excel and I think you are the right guy's to give me a hand with it.

I have an excel sheet that I use for flight simulation and between other things, I want to create a small compus with shapes(arrows) that rotates whenever I add a value to a specific cell. So far I managed to do it but only with a single arrow. When I try to add a second shape it says "Ambiguous name detected worksheet_Change".

I need 2 arrows because one indicates the heading of the aircraft and another indicates the wind.

On VBA I managed to do the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub

This gives me only the rotation for one of the arrows (shapes), do you have any idea how do I add a second one?

https://drive.google.com/file/d/0B6JMHUkV2NKJRTZVUmRBZER6bVE/view?usp=sharing  On this link there's a picture to give you the idea of what I'm trying to do.

If you need any other information from me, please let me know.

 

 

Thank you very much for all the help you can give me.

 

 

Kindly,

 

 

Edgar Freitas

05 May 17 at 07:05

Hi Edgar, that sounds like a cool idea!

The "ambiguous name detected" error occurs when you create multiple procedures in the same scope with the same name. What I imagine has happened is that you've copied and pasted the Worksheet_Change subroutine to create a separate procedure for the second arrow? Sadly, you can't do it this way - you can only have one Worksheet_Change subroutine per worksheet. The simple solution is to add the code for the second arrow to the same subroutine as the first.

I hope that helps and good luck with the project!

05 May 17 at 18:30

Andrew, I can't thank you enough!! It worked perfectly!!! :D

Thank you so much for your help.

 

Have a great weekend.

 

 

Kindly,

 

Edgar Freitas

05 May 17 at 16:40

Hello Andrew,

Thank you very much for your reply. 

That's exactly what happened. I thought I could simply copy and paste it to create new arrows.

Your solution is: "...to add the code for the second arrow to the same subroutine as the first."

As I'm not good with VBA, could you please indicate me how to do this? I already tried but I think it's not the right way of doing it as I got an error saying "Duplicate declaration on the same scope".

This is how I did it:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
    Dim KeyCells As Range
    Set KeyCells = Range("Z3")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z3")
    ActiveSheet.Shapes.Range(Array("seta2")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub
 

Thank you once more Andrew!

 

Kindly,

 

Edgar Freitas

Andrew G  
05 May 17 at 17:29

Hi Edgar,

In the same way that you can't declare two subroutines with the same name in the same module, you can't declare more than one variable with the same name in the same subroutine.  Simply remove the duplicate Dim statements so that you don't have more than one variable with the same name in a single procedure.

I hope that helps!