We know that in Excel, you can type a few numbers and use the *fill handle *to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

The post Quick tip: Make a list of numbers (or dates) in Power Query easily appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>We know that in Excel, you can type a few numbers and use the *fill handle *to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

Simple, just type ={1..10} in the Power Query formula bar to get the numbers 1 thru 10. See this in action.

**What formula bar? I don’t see any: **If your Power Query is devoid of formula bar, just go to view ribbon and slash “Formula bar” option with your master sword. You will now acquire the magical power of formula bar. You can see and edit mysterious M language instructions now.

Of course they are. You just need to unleash a bottled fairy on those numbers. Or take the easy route and use =List.Numbers() function. Go ahead, it won’t bite you.

**List.Numbers(1,10)**: Same as {1..10}**List.Numbers(1,10,2)**: You get numbers {1,3,5…,19}*ie*first 10 odd numbers**List.Numbers(0,10,2):**Take a guess… what do you think this will do?**List.Numbers(0,100,10):**You get 100 multiples of 10, starting with 0, ending at 990

You can also get raisins, dried apricots and cranberries, but only on the bulk-food aisle. If you just want dates, as in 13th of November 2018, then you can use **List.Dates() function.**

**Examples of List.Dates():**

Unlike simple numbers and text values, dates & durations need to be typecasted. We use #date, #duration to generate the required format first. Keep that in mind when reading below examples.

**List.Dates(#date(2018, 1, 1), 365, #duration(1, 0, 0, 0)):**You get all 365 dates in year 2018, starting with 1 January 2018.**List.Dates(#date(2018, 11, 13), 31, #duration(1, 0, 0, 0)):**You get 31 days from 13th of November 2018.

Try with other numbers in duration to see what happens.

In all these cases, Power Query spits out a list. While it looks like a table, list is different and can ever have only one column. So if you want to do something with the list, you need to convert this to a table. This is a simple matter of spilling magic potion on the list using the List tools > Transform > “To Table” button.

Now that you have a table, you can add columns, sort or do other cool things easily.

I use a variation of {1..10} or List.Dates on most of my Power BI files when I need to make up some data.

**What about you? **How do you like this tip? Share your Power Query wins in the comments.

I am off to D&D now – Dinner and dungeon time that is. The seven maidens can’t be saved with Power Query. Tada…

New to Power Query? Check out this great intro.

The post Quick tip: Make a list of numbers (or dates) in Power Query easily appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>This post will describe how it works and how it was made.

The post Hui’s World ! – An Excel Project of Global Scale. appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>This post explores the first of them, **Hui’s World**.

**Hui’s World is an animated Excel Chart which displays the Earth and key features.
**

Hui’s World consists on an Excel chart comprising 111 series containing 2,775 data points. The 111 series are supported by 380 Named Formula. The rotation is driven by 10 lines of VBA code.

Hui’s World displays the following items:

- The planar projection of the World as a Sphere with a select number of countries/continents;
- The lines of Latitude and Longitude, International Date Line and Greenwich datum line (Prime meridian) and Arctic/Antarctic circles;
- Major cities throughout the world;
- The location of all the Chandoo.org Excel Ninja’s;
- The States boundaries and State Capital cities in Australia;
- Labels of Latitude, Longitude and Key Global points and optionally;
- A User Defined Location (Marker and Label)

The globe can be rotated manually or animated with user control of the speed and direction of the rotation.

Many of the above items can be toggled to be displayed or be hidden, even as the globe turns using toggle buttons.

The Country boundaries, Cities and Lines of Longitude can be shown as if the earth is transparent or they can be hidden as they pass around the back of the earth from your view point.

There are a number of predefined views for major countries/continents and other features.

This post will look at how Hui’s World was constructed.

It will not be focusing on the math behind model, but that will be mentioned from time to time where it is important.

**Apology**: In simplifying the world to 2,775 points, compromises have been made. So if your Country, City etc is missing, simplified or merged to your neighboring countries I apologize in advance.

If you want to download Hui’s World click here: Download Hui’s World; Download Hui’s World Pre Excel 2007

If you want to download the Helper File click here: Download Hui’s World Helper File

Hui’s World uses an Excel Scatter Chart as the basis of the Chart.

Excel scatter charts do not understand latitudes and longitudes, they only understand a standard orthogonal x & y grid. As such we need to convert the data into the system that Excel understands.

Excel performs the trigonometry functions according to the Anti-clockwise rule.

The World uses a similar coordinate system where the base or 0 Azimuth is the Greenwich time line. All Azimuth values are taken from here.

As such we can use the Latitude and Longitudes for the locations and apply some math to work out where things should be plotted.

As such we need to determine the Location of each point of each data item and how it will be plotted on a 2D X/Y plane. I have assumed that we will look at the globe from a point level with the equator.

Data was required for the location of all the elements required to be displayed. Data for the project was sourced from freely available sources.

This includes:

Even though the Country data is Low Resolution, the original file still contained 26,264 points.

Before I even started I knew that I would have to simplify this data set, if for no other reason than moving twenty thousand points in an Excel chart would have speed implications.

As a Mining Engineer I have access to a number of mining / GIS related software tools.

I used one of these, to allow me to import the various GIS files, edit the files and save the edited data out as a CSV file type.

I could have also used any CAD style program just as simply.

The World Country Co-ordinates file was a CSV File containing the Latitude and Longitude coordinates and Names for all the worlds countries.

As a CSV File I imported it into a mine planning package as X & Y values.

Next I manually digitised around the key countries and continents I wanted to display. Whilst digitising I snapped to points to ensure a basic level of geographical accuracy.

This resulted in a simpler file that now only contained 1,400 points.

The new simpler file was written out as a CSV file.

Most CAD package could have been used to perform this.

In fact a number of Free Online websites also offer SHP to CSV file conversion

eg: https://mygeodata.cloud/converter/shp-to-csv

Working with the Latitudes and Longitudes in an X/Y Display system results in the file being displayed in an Equirectangular projection, with exaggerated distances in the Lower and Higher Latitudes. ie: Greenland isn’t that large. In this case that doesn’t matter as the resultant file is saved in Latitudes and Longitudes and it is the Latitudes and Longitudes that are read into Excel and then converted to X/Y values on a Spherical projection where they appear in the correct locations.

I repeated the process for Australia and included the states. This took the source file with 108,000 data points down to a manageable 210 data points.

This data was also written out as a CSV file.

These CSV File contained columns of Line ID, Latitudes, Longitudes and sometimes other data.

ie: ID 1 in the first column above is Western Australia and ID 2 is Northern Territory etc

I used a similar process to that described above downloading a set of co-ordinates for the International Date Line as well as Major Cities of the world.

The Chandoo.org Ninja locations was sourced from Chandoo.org and the Cities coordinates manually sourced from World Atlas.com and saved in Excel.

The text files above were imported into Excel as Comma Delimitered Text Files and cleaned up.

Header and Footer lines were removed, Segment break lines were cleared and The Names were shifted to the First Column.

Next was to standardise the data.

The GIS data downloaded came in a number of formats. Most typically Longitudes are expressed in terms of Degrees East (positive) and Degrees West (negative) from the Prime Meridian.

For the purposes of use in Excel it was simpler to simply have the Longitudes expressed as degrees of a circle ie: starting at 0 deg at the Prime Meridian and extending to 360 deg. I choose to use East as Positive as I am used to having Perth as +115 Deg in my day job.

This was done using a helper column where required.

Eg: **E3**: =360+D3

Similarily with Latitudes, some data was sourced where zero degrees was North pole and 180 deg was the South pole.

See Column B below

The data was standardised to have Zero degrees at the equator with positive 90 Deg at the North Pole and –90 Deg at the South Pole.

Once again helper cells were used if required to transform the data to this format.

Note the Formula in **C1043**: =90-B1043

Any system of locations could have been used. I just choose to use what I am familiar with. With other coordinate systems the math would need to be set up accordingly.

To add animation to the system we will need to have the following data sets

- Original data, Latitudes and Longitudes
- Transformation data, Details of the required view azimuth
- Transformed data, The transformed data after the Transformation data is applied to the Original data.
- Projected data, The Projection of the transformed data onto a 2D viewing plane

Once we have the projected data we can plot the data.

By changing the transformation data above and recalculating the worksheet, the chart will update and give the user the impression of animation.

But for this to work smoothly it needs to be fast.

One of the main skills I learned from completing [sic] the Excel Hero Academy was the benefit of using Named Formula. Named Formula store data in arrays that are stored in Memory. As such they don’t need to access the Excel grid and so performance is significantly improved.

But as I already have point data for 25 countries, 7 Australian States, 36 Lines of Longitude and 17 lines of Latitude, Cities of the World, Australian Capital Cities and the Chandoo.org Ninjas, and need between 4 and 5 sets of data for each plotted line, resulting in a total of 374 Named Formula, I could see that if I wasn’t organised and methodical this could become a real mess.

What was needed was a Named Formula Naming Convention.

To plot a line on a Scatter Chart within Excel we need to obtain the X & Y coordinates for each point along the line.

The data we have imported is a series of Latitude and Longitudes for each point.

Each line on the chart will be a Chart Series and will be made up of an array of x and an array of y values

Each Array of x and y values will be derived from the appropriate Latitudes and Longitudes. And each series may need a list (array) of values for annotation, eg: City names.

The following naming convention was setup and used for the project

Data type_Name_Type of data

Hence

**Data Types** used:

Countries, **C**

Cities, **Cit**

Ninja’s **Nin**

Latitude Line, **Lat**

Long Lines, **Lon**

Other Lines **o **Other data

Variables **v ** Used to store variables to interact between the user controls, VBA and the Named Formula

Boolean ** b** Boolean switches to toggle controls on/off

**Names** used:

Africa, Japan, etc

**Type of Data**:

Latitude Lat

Longitude Lon

X Value x

Y Value y

Names Nam

A Separator of an _ (underscore) was used to separate the fields

**Example**:

Countries:

C_Africa_Lat

C_Africa_Lon

C_Africa_X

C_Africa_Y

Cities:

Cit_CoW_Lat

Cit_CoW_Lon

Cit_CoW_X

Cit_CoW_Y

Cit_CoW_Nam

Lines of Latitude

Lat_010N_Lat

Lat_010N_Lon

Lat_010N_x

Lat_010N_y

Lat_010S_Lat

Lat_010S_Lon

Lat_010S_x

Lat_010S_y

Lines of Longitude

Lon_010_Lat

Lon_010_Lon

Lon_010_x

Lon_010_y

The benefits of using a Naming Convention like this is that in the Name Manager with Excel the Names are grouped and sorted according to there names. As such it simplifies the editing of formula.

We can transform the data from Latitudes and Longitudes to X & Y values using some simple math.

Pic

X = COS(RADIANS(C_Alaska_Lat))*Sin(RADIANS(C_Alaska_Lon + View_Az))

Y = SIN(RADIANS(C_Alaska_Lat))

So using Alaska as an example

The following formulas were added next to the top of the data set

You will notice in the above formula that we need to have a Named Formula setup to store and access the Latitudes and Longitudes for each data set.

Once again to allow for maximum performance it was chosen that the Latitudes and Longitudes would be stored as Named Formula, rather than being accessed directly from Excel ranges.

Using Alaska as an example

**C_Alaska_Lat** ={55.122;57.643;59.151;58.655;60.555;62.417;63.869;65.189;66.448;68.351;70.307;71.163;69.968;69.642;60.307;60.899;57.604;54.941;51.213;55.122}

**C_Alaska_Lon ** ={196.677;202.293;203.221;198.887;194.575;195.364;199.223;193.038;198.399;193.172;198.058;204.413;215.048;218.997;219.004;212.925;207.849;200.566;180.895;196.677}

We can see above that we need to establish at least 4 Named formula and sometimes 5 for each item we wish to plot.

The fifth Named Formula will hold the City Names etc for annotation, where required.

Using Alaska as an example 8 formula were added adjacent to the Country data

These allow for the following constructions

Column A contains the name of our country, in this case Alaska

Column B contains a line identifier that is an artifact from the CAD processing. It has been maintained so that countries could be separated and identified

Column C & D are the Latitudes and Longitudes of the points which were digitised for Alaska

Column E Is the corrected Longitudes of the points which were digitised for Alaska going from 0 to 360 deg

Column G is a list of Named Formula names. Each is constructed suing a formula.

**G3**: C_Alaska_Lat =”C_”&A3&”_lat”

**G4**: C_Alaska_Lon =”C_”&A3&”_lon”

**G5**: C_Alaska_x =”C_”&A3&”_x”

**G6**: C_Alaska_y =”C_”&A3&”_y”

Column H is a series of formula that will setup the formula for the Named Formula

**H3**: =”={“&Concat(C3:C22,”;”)&”}”

={55.122;57.643;59.151;58.655;60.555;62.417;63.869;65.189;66.448;68.351;70.307;71.163;69.968;69.642;60.307;60.899;57.604;54.941;51.213;55.122}

**H4**: =”={“&Concat(E3:E22,”;”)&”}”

={196.677;202.293;203.221;198.887;194.575;195.364;199.223;193.038;198.399;193.172;198.058;204.413;215.048;218.997;219.004;212.925;207.849;200.566;180.895;196.677}

**H5**: =”=COS(RADIANS(“&G3&”))*COS(RADIANS(“&G4&” + v_View_Az))”

=COS(RADIANS(C_Alaska_lat))*COS(RADIANS(C_Alaska_lon + v_View_Az))

**H6**: =”=SIN(RADIANS(“&G3&”))”

=SIN(RADIANS(C_Alaska_lat))

The Formula in **Columns G & H** are setup to return a Text String, that replicates the formula required for the Name Manager.

The formula in Cells **H3** and **H4** use a User Defined Function, **Concat()**, to append the Latitude and Longitude values into a string with ; as a separator and appropriate ={ and } leading and trailing brackets.

I modified the Concat UDF to allow an optional Surrounding character ” as well as to skip over blank cells.

This change allowed Names to be stored as strings and also allowed large ranges to be uploaded at once

Eg:

={test1;test2;test3} is not permitted as a Named Formula formula unless test1, test 2 and test3 are existing Named Formula

={“test1”;”test2”;”test3”} is permitted, as test1 is a Text string.

**H5** & **H6** setup formula to return the X & Y values for each of the Points defining Alaska. Note that these formulas make use of the Named Formula in **H3** and **H4** above them. They also refer to a Named Formula **v_View_Az**. We haven’t set this up yet, but don’t worry about that yet. It simply contains the viewing azimuth of the view that the user wants to see the globe from, ie: How far to rotate the data so that it looks like the globe has turned.

The use of Concat() UDF was described in a previous post at: Concat

Finally we need to load these new named formula into the Name Manager

We could do this manually, by selecting each range and ….

No, no no

In one of my previous posts I describe a technique to load named Formula from the worksheet. It is described in: Automating Repetitive Tasks

The Automating Repetitive Tasks post above also describes the use of a small pieces of VBA Code which will allow these named formula to be uploaded quickly. This code is supplied included in the **Hui’s World** model.

For Alaska Select **G3:G6**

Execute a VBA Macro by using **Alt+F8**

** **

Select **aa_Load_Named_Ranges** and press **Ok**. The **aa_** prefix was simply added to the macro’s name so that it appears at the top of the Module List and is hence easier to find. So easy that you don’t need to select it, simply press **Enter**. This will be discussed in the section **Formula Updates**.

The 4 Named Formula selected were just created or updated if they previously existed.

Each Named Formula contains the formula comprised of the text to the right of it in Column H

Now just repeat this for the other Countries, Cities and other details.

Or setup all the countries and other data and upload them all in once step.

The **aa_Load_Named_Ranges** module skips blank lines, so you can setup a whole block of these adjacent to each set of data and then upload the entire lot by selecting say **G1:G2000**, then running the **aa_Load_Named_Ranges** module described above.

Once the Named Formula for the Countries, Cities and Date Lines we can add a chart and upload each Country as a series

Select a single cell then got the **Insert** Tab and click on the **Scatter Chart** Icon,

Select any of the Scatter Charts as we will be formatting the Chart’s series later on.

Take note of the Charts Name

Each series requires a Name, a Range for X values and a Range for Y Values.

These are usually entered via the Add Series Dialog shown above.

However as we will require some x & y series to be added it is a lot easier to use some VBA to add the series.

For Alaska we have the Name: **Alaska**, with the X Series: **C_Alaska_x** and Y Series: **C_Alaska_y**

Again we can do this manually for each of the data sets or we can set this up and then use a small VBA snippet to add all the series to the chart in one go.

To achieve this I will use another code from the Automation post, **Add_Cht_Series**

To run this I setup 3 formulas for each series, then copied these down and relink the ranges for the other series

Using Alaska as the example

**I10**: =”=”””&A3&””””

**J10**: =”=Globe!”&G5

**K10**: =“=Globe!”&G6

Setup a list of Country names, with adjacent x & Y values named Formula names

Note they are offset from columns **G** & **H** to avoid being uploaded as Named Formula

You can add other formula for the other ranges below eg: USA and Canada etc

Then Select the First column and run the **Add_Cht_Series** macro by pressing **Alt+F8** the select **Add_Cht_Series** and press **Run**.

The Add_Cht_Series code will select every cell in the first column and add a New Series to the Chart based on the Values in the 3 Columns I, J & K.

Lines of Longitude are the vertical lines or Great Circle Lines that extend North & South from the North Pole to the South Pole.

For each line we know the Longitude it will have as it is a fixed Longitude or Azimuth from the Prime Meridian.

eg: The Longitude +120 Deg is North South line passing through Western Australia.

Pic

We know that it extends from -90 Deg to +-90 Deg.

So we can use a Named Formula to calculate the Latitudes and the Longitude for the line.

Because the Latitudes of the lines of Longitude are know and are the same for each line of Longitude I setup a Named Formula _t2

**_t2**: ={-90;-80;-70;-60;-50;-40;-30;-20;-10;0;10;20;30;40;50;60;70;80;90}

This named formula of Latitudes can be used for all the lines of Longitude.

We don’t need to store the Longitude for each line as it is fixed

We can directly calculate the X & Y values for each line of Longitude

The X Value is calculated by:

**Lon_030_x**: =COS(RADIANS(_t2))*SIN(RADIANS(360-30 + v_View_Az))

This is setup as before for all lines of Longitude and uploaded to the Name manager as described before

The Y Value is calculated by:

**_y2**: =SIN(RADIANS(_t2))

The _y2 Named Formula is then used for all the Lines of Longitude.

That is there is no Named Formula **Lon_010_y**, **Lon_020_y**, **Lon_030_y** etc, as they are all the same **_y2.**

Lines of Latitude are the horizontal lines that are parallel to the Equator and points on the lines are the same distance from the North or South poles.

For each line we know the Latitude it will have as it is a fixed Latitude or Bearing North or South of the equator.

eg: The Latitude 30 Deg south or -30 Degrees is the East West line passing just north of Perth Western Australia.

Pic

The important part to realise here is that we could easily setup a Named Formula for the Latitude and Longitude for these lines.

Lat_010S_Lat: ={-10;-10;-10 … -10;-10;-10}

Lat_010S_Lon: ={0;10;20;30;40 …. 340;350;360} or =(ROW(OFFSET($A$1,,,37,1))-1)*10

and then calculate the X & Y values for each circle.

But there is an easier way.

Because I haven’t implemented vertical tilting of the earth yet, the lines of Latitude will always be straight and extend from one side of the globe to the other.

Knowing this I can cheat and simply workout the starting and finishing x & y coordinates for each line

This is done using the following formula:

**Lat_S010_x**: ={-1;1}*SIN(RADIANS(100))

={-0.98; 0.98}

This formula calculates the Sin of the Angle 100 and then multiplies it by the array {-1;1}

In this example this creates the X values of -0.98 and 0.98

Similarily we know the Latitude of the Line of Latitude and so we can use that to calculate the Y Values for each line

Note here that we use the Multiplication array of {1;1}

**Lon_S010_y**: ={1;1}*COS(RADIANS(100))

={0.17; 0.17}

In this example this creates the X values of 0.17 and 0.17

Excel then plots these two points as a Flat Line at -10 Deg South, extending from -0.98 to +0.98.

Note that we haven’t stored an array of Latitudes and Longitudes saving both a Named Formula and saving calculations

The same technique described for the Lines of Latitude was applied to the Arctic and Antarctic Circles as well as the Tropic of Cancer and the Tropic of Capricorn lines

The Arctic Circle is located at: North 66.50 Deg, The Antarctic circle is located at South 66.55 Deg.

The Tropic of Cancer is located at: North 23.5Deg, The Tropic of Capricorn is located at South 26.43 Deg.

One of the first issues that arose during construction of the model is that data was being displayed in its normal projection but also in the negative projection as the various objects traveled around the back of the globe as the globe rotated.

Technically this is what would be seen if the Earth was made of Glass.

But this isn’t what we are used to seeing.

I have used two techniques for solving this

For simple objects like Lines of Longitude, Markers and Annotation etc, the easiest way was to check the location of the object and if it was behind the earth, simply add a large number so that it plots outside the Plot area for the chart.

As the chart extends from -1 to +1 in both x and y directions, it was as simple as adding a large number say 5 to the data

This is simple and works well with minimal overheads.

It works well in that it shifts the entire object off the Charts’ Plot area.

Eg:

The basic formula for the Lines of Longitude at 210 Deg East:

=COS(RADIANS(_t2))*SIN(RADIANS(360-210 +v_View_Az))

The adjusted formula to allow Hidden Line removal:

=COS(RADIANS(_t2))*SIN(RADIANS(360-210 +v_View_Az)) + 5 * AND((AND(IF(210<v_View_Min,210+360,210) > v_View_Max, IF(210<v_View_Min,210+360,210)<(v_View_Min+360))), b_View_Hidden)

The Red part of the formula above is what checks the location of the line of Longitude and if it should be hidden shifts the location off the chart’s plot area (+5). It also incorporates the logic to enable the Toggling on/off of Hidden Lines, using the b_View_Hidden name.

Despite being complex, the logic is fairly simple, and it is great for lines of Longitude and for controlling the location of the the various annotations on the chart.

But because of this it can’t be used for when a country or continent starts to move behind the globe. Ideally we want to just hide the bits of the countries as they go off the view plan of the earth, not the whole country.

For more complex objects like Countries a number of techniques were investigated, including:

The use of VBA to hide line segments was considered, but that would mean checking every line segment every time the chart is updated. If the segment was behind the Globe, then the fill color of the line could be set to None.

This was dismissed as potentially adding too much overhead to the chart.

The next technique was to plot every line segment as a series of 2 points. Doing this would mean that I could use the same technique that was used for simple objects and simply plot them outside the charts plot area. The downside here was with 1,275 data points there would be 2,550 named formula and 2,550 chart series.

No, there was a better way.

I have been having some conversations with Peter Bartholomew, Peter is well know for his innovative uses of Named Formula.

Peter is a former Senior Fellow at QinetiQ and is internationally known for his research in the area of the optimal design of aeronautics structures, having published over 80 papers. Peter has interests in data management and graphic design that are evident in his use of Excel to capture and convey information. He has made a particular specialty of the programmatic use of shapes in Excel to create diagrams as controls and also moderates a leading Excel Group on LinkedIn. Peter can been seen trolling around the Excel communities at the Linkedin Excel Hero Group and here at the Chandoo.org Forums amongst others.

Peter suggested changing the x Value of the data for all points behind the earth so that they plot on the boundary of the globe. Then plotting a Black or White circle in front of the earth circumference so that the edge lines were hidden. The good thing about this technique is that only the X Values need to be checked and altered, cutting down processing effort.

Peter then also gave me the formula to implement this:

So using Africa as an example

Original Formula: =COS(RADIANS(C_Africa_Lat))*SIN(RADIANS(C_Africa_Lon+v_View_Az))

Hidden Line Formula: =IF(COS(RADIANS(C_Africa_Lon+v_View_Az))>0, COS(RADIANS(C_Africa_Lat)) * SIN(RADIANS(C_Africa_Lon+v_View_Az)), IF(b_View_Hidden, COS(RADIANS(C_Africa_Lat)) * SIGN(SIN(RADIANS(INDEX(C_Africa_Lon,1)+v_View_Az))), COS(RADIANS(C_Africa_Lat)) * SIN(RADIANS(C_Africa_Lon+v_View_Az))))

I owe a huge gratitude of thanks to Peter for this assistance on this as it took the visuals from being great to being stunning.

You can see the hidden edges of the countries from behind the globe on the same view as the image above. The Areas of the countries resting on the edge of the globe are highlighted below.

As nice as the display of the world maybe, the project couldn’t cater for every location on the planet.

So I added the ability to display a user selected point from a predefined list or to add a user defined location for a specific point.

I setup 4 predefined locations, Ayers Rock, Great Barrier Reef, Grand Canyons & Pyramids of Cheops.

These are user selectable from a Drop Down menu

or by choosing User Defined, the user can define there own location

This allows the user to enter the Coordinates and Name for a single point which is then plotted.

In the example above we can see that the Amazon Rain Forest has been plotted at the correct location of -3.8 deg South and 297.5 Deg East.

The User Defined Location is setup using 5 Named Formula:

o_UDL_Lat: =IF($P$31=”User Defined”,P32,INDEX(o_UDL_Latlist,MATCH($P$31,o_UDL_NamList,0)))

o_UDL_Lon: =IF($P$31=”User Defined”,$P$33,INDEX(o_UDL_LonList,MATCH($P$31,o_UDL_NamList,0)))

o_UDL_Nam: =IF($P$31=”User Defined”,$P$34,INDEX(o_UDL_NamList,MATCH($P$31,o_UDL_NamList,0)))

o_UDL_x: =IF(LEN(o_UDL_Lat)=0, 2, COS(RADIANS(o_UDL_Lat))*SIN(RADIANS(o_UDL_Lon+v_View_Az)))

o_UDL_y: =IF(LEN(o_UDL_Lon)=0, 2, SIN(RADIANS(o_UDL_Lat)))

The Names controlling the Predefined locations are:

o_UDL_LatList: ={-25.345,-18.3,36.1,29.98}

o_UDL_LonList: ={131.035,147.7,247.89,31.13}

o_UDL_NamList: ={“Ayres Rock”,”Great Barrier Reef”,”Grand Canyon”,”Pyramids”}

These contain the locations and Names of the 4 predefined locations.

If **None** is selected as a Predefined location then the User Defined Location is not plotted.

Note the formula has been adjusted so that if the Latitude or longitude cells are blank the data point is plotted off the charts plot area, .

Annotation of the model has been handled in exactly the same way as the countries has been described above except that for the annotation the Series in the Chart have no line, Markers are enabled and set differently for each data type and Data Labels have been used to annotate the points values eg: City Names etc.

In the example below I have set the Australian/NZ Capitals charts series line to be Red instead of None

You can see by the shear number of Named Formula that the management of them was a critical aspect of the construction of Hui’s World.

However it was a lot simpler than you may imagine.

I use a technique where the formulas are developed on the worksheet and uploaded into the Name Manager.

Let me demonstrate with two examples

The basic formula for the conversion of the Lat and Long arrays to X & Y for each country are shown by

x value: =COS(RADIANS(C_Africa_Lat))*SIN(RADIANS(C_Africa_Lon+v_View_Az))

y value: =SIN(RADIANS(C_Africa_Lat))

These two names have to be made for every country

So I setup a list of the 46 countries

Next to the list I added formula to develop both the Named Formula’s Name as well as the Function for the named Formula

Once these formulas are setup I simply select the column of Named Formula Names and run the macro **aa_Load_Named_Ranges**

Using this technique allows very rapid prototyping of new formula

The corresponding X Value formula for the countries is

x value: =COS(RADIANS(C_Africa_Lat))*SIN(RADIANS(C_Africa_Lon+v_View_Az))

This is applied to the list of countries using

=”COS(RADIANS(C_“&E368&”_Lat))*SIN(RADIANS(C_“&E368&”_Lon+v_View_Az))”

The x Values formula to allow Hidden lines is

=COS(RADIANS(C_Africa_Lon+v_View_Az))>0 COS(RADIANS(C_Africa_Lat))*SIN(RADIANS(C_Africa_Lon+v_View_Az)) b_View_Hidden COS(RADIANS(C_Africa_Lat))*SIGN(SIN(RADIANS(INDEX(C_Africa_Lon,1)+v_View_Az))) COS(RADIANS(C_Africa_Lat)) C_Africa_Lon+v_View_Az

But using this technique a formula of

=”=IF(COS(RADIANS(C_“&E368&”_Lon+v_View_Az))>0,COS(RADIANS(C_“&E368&”_Lat))*SIN(RADIANS(C_“&E368&”_Lon+v_View_Az)),IF(b_View_Hidden,COS(RADIANS(C_“&E368&”_Lat))*SIGN(SIN(RADIANS(INDEX(C_“&E368&”_Lon,1)+v_View_Az))),COS(RADIANS(C_“&E368&”_Lat))*SIN(RADIANS(C_“&E368&”_Lon+v_View_Az))))”

is developed once and then applied to the list of countyries

Similarily for the X Values for the Lines of Longitude

The formula including allowance for hidden line removal is:

=COS(RADIANS(_t2))*COS(RADIANS(MOD(v_View_Az+10,360)))+5*AND(10>MOD(v_View_Max,360),10<MOD(v_View_Min,360),b_View_Hidden)

In this case a list of 10 to 360 was used

The Named Formula Name and Named Formula Formula were setup as shown above.

Then the entire range was loaded in one pass into the Name Manager using **aa_Load_Named_Formula**

A number of controls were included in the model.

These controls allow the following functionality

**View Point Slider** – Allows the rotation of the earth to the desired view point.

This is a simple slider control linked to a Named cell **v_View_Az_Entry**

**Animate/Stop** – Start rotation of the model and once started stops the model

This is a control button and is linked to the Macro: **StartStop**

**<< **/ **>>** – Change direction button

This is a control button and is linked to the Macro: **Change_Direction**

**Reset** – Reset button. Resets the model to the starting conditions

This is a control button and is linked to the Macro: **Reset**

**Rotation speed** – Allows the change of rotation speed and direction from -10 degrees per step to +10 degrees per step

This is a Spin Control and is connected a a Named Cell: **v_Rotate_Speed_Link**

Six predefined views have been established for viewing the model from preset locations. This includes Australia, the Americas, India, Africa/Europe, The Pacific and Greenwich Time Line (The Prime Meridian).

Each button is a Shaped linked to a macro that rotates the model from the current view point to a pre-defined view point for that position.

example:

All code is available in the model, by pressing **Alt+F11**.

Check box controls were added to enable or disable the visualization of 7 sets of data.

Each checkbox is connected to a Named Cell on the worksheet.

These names are then available for use in the various formula to hide or view data as appropriate.

Animation is achieved by use of a small piece of VBA code that simply adds a value, the rotation speed, **v_Rotate_Speed**, to the View Azimuth **v_View_Az_Entry**.

If the new view azimuth is greater than 360 it subtracts 360 degrees from the new view azimuth

If the new view azimuth is less than 0 it adds 360 degrees to the new view azimuth

All code is available in the model by pressing **Alt+F11** to enter the Visual Basic Editor.

The modularisation of the code and use of worksheet cells to store key variables allows for interactive control of the model whilst it is running

that is you can press any of the controls eg:** <<**, **>>** or the Speed change spinner while the model is running and they will have an immediate effect on the model.

The background of the chart was originally set to White and then Black.

However as I regularly use Google Earth, I though of replicating the stars that rotate as the globe rotates behind the Earth in Google Earth, but without the rotation. I was already sick of Rotation by this point.

To achieve this I setup a view in Google Earth that had an appealing background

Then simply took a Screen shot of the Google Earth screen

I then added a Grey Circle to cover the view if the earth, so that only the Background was visible.

This image was loaded as the background to the Chart Area.

This involved a bit of fiddling to get it the right size and centered but I think the impact is much better.

**Zooming:** It would not be hard to add the ability to zoom in/out on areas of the chart.

**User Defined Data:** Similar to the Point Input it would be easy to add the ability for a user to add a line or number of lines of data including annotation.

**Earth Tilt: **The Earth doesn’t rotate on a vertical axis. The earths rotational axis is tilted about 23.5 deg and is constantly changing. The ability to alter the tilt was considered and then put into the version 2 basket.

ps: I already have a working version of Hui’s World with Axis tilt and will release it quite soon.

Note the countries are rotating about a 23 deg tilted axis, I haven’t updated the Latitudes or Longitudes as yet.

**Speed Improvements:** I have considered saving the Latitudes and Longitudes in arrays of Radians instead of Degrees. ie: Pre-process the Degrees into Radians before uploading them. This would remove the need to convert the Latitudes and Longitudes to radians on the fly. The downside is that it makes checking and editting formula much more difficult.

If you want to download Hui’s World click here: Download Hui’s World; Download Hui’s World Pre-Excel 2007

If you want to download the Helper File click here: Download Hui’s World Helper File.

My previous animated chart is: 3D Dancing Pendulums

I have two more advanced chart projects in mind and both are way beyond this project in scope…

The first is at a proof of concept stage and works. The second is still bouncing around in my head.

If you have any ideas or suggestions for similar projects please let me know in the comments below:

This project has been on my bucket list for several years. Probably since Keyhole developed what is now Google Earth.

Most of that time was spent on spreadsheet/data design thoughts. The actual implementation time was just under 20 Hours spread over about 3 months. In fact it has taken about twice as long to write this post as it did to develop the model.

I’m quite proud of how it has turned out I would love to hear your thoughts and comments about this model in the comments below.

And a final Thanx to **Peter Bartholomew** for his assistance with the Hidden Line logic.

**Why did I do it? Because they said it couldn’t be done.**

If you have suggestions for other Excel models please also leave them as a comment below:

The post Hui’s World ! – An Excel Project of Global Scale. appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>*This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.*

The post Leave entitlement vs. usage analysis with Power Query appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>*This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.*

The problem is simple. We have a bunch of employees with their leave entitlement. We also know how many leaves they took.

Given these two tables, you want to answer questions like:

- Which employees took
**exactly**100% of their allocated leave? - Which people never took any leave?
- Who are the people taking 100% of their leave?

If you also know when an employee took the leave (date), you can ask questions like:

- Assuming leave entitlement resets at start of year (1 Jan), which employees are using 100% of their entitlement

While we can use Excel formulas (or VBA, pivot tables + power pivot etc.) to answer the questions above, I think using Power Query is the simplest way to crack this.

I made a quick video explaining the problem, solution, how to enhance it when you have multi-year data. Check it out below (or on our Youtube channel).

You will also learn:

- How to merge (join) two tables in Power Query
- Using basic & advanced
**group by**in Power Query - How to extract year from date values
- Replacing errors with some value
- Creating conditional columns in PQ

**Click here to download the solution workbook** for this topic. Examine the Power Query definitions to learn more.

I love how versatile and elegant Power query based solutions are. What about you? Do you use PQ to solve problems like this? Please share your experience / feedback in the comments section.

The post Leave entitlement vs. usage analysis with Power Query appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>You have two tables - emps & leaves as illustrated below.

Your mission is to find out answers to below questions.

- How many employees used
**exactly**100% of their entitled sick leave? - How many employees did not take any sick leaves?
- Listing of all employees who used 100% of their entitlement

Use either Power Query, Excel formulas or any other technique to answer the questions.

The post How many people used their entire sick leave entitlement? [Power Query / Excel homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>So let’s keep this quick and simple. I want you to figure out an elegant and simple way to answer below questions.

Imagine you are the HR analyst at BigLargeCompany. You need to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

Your mission is to find out answers to below questions.

- How many employees used
**exactly**100% of their entitled sick leave? - How many employees did not take any sick leaves?
- Listing of all employees who used 100% of their entitlement

Use either Power Query, Excel formulas or any other technique to answer the questions.

**Please download sample data for this exercise here**.

Once you have answers, post them in comments section.

**Want more problems? **Check out Excel homework section for some very tricky, interesting challenges.

Talk to you soon. If you need me, I will be scraping ketch up off carpets.

If you want to learn how to solve problems like this, check out the **Entitlement vs. usage analysis – solution** page.

The post How many people used their entire sick leave entitlement? [Power Query / Excel homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Let's say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can't convince VLOOKUP to act nice.

*Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here? *

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let's learn how to do lenient lookups.

The post Lenient lookup [Advanced Formula Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can’t convince VLOOKUP to act nice.

*Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here? *

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let’s learn how to do lenient lookups.

Let’s say you have a simple 2 column table like this. Our table is uninspiringly named *data.*

Our input amount is in cell **C3**.

Let’s say when looking up for the amount, we want to follow this logic.

- If an exact match is found, return that
- Else, see if we can find anything with in 50 cents either side (you can change 50 to whatever you want)
- If nothing can be found, we want to return “Not found” or similar message

**Formulas to use:**

1: we can use good old INDEX+MATCH

2: we can use array based INDEX+MATCH

3: we can use IFERROR.

Let’s put everything together.

Our lenient lookup formula (array):

`=IFERROR( INDEX(data[Client], IFERROR(MATCH($C$3,data[Amount],0), MATCH(1, (data[Amount]>($C$3-0.5))*(data[Amount]<($C$3+0.5)),0) ))`

,"Not found")

**How does it work?**

Let’s go inside out.

**MATCH($C$3,data[Amount],0):** this formula simply looks for C3 in data[Amount] column and returns the position.

**MATCH(1, (data[Amount]>($C$3-0.5))*(data[Amount]<($C$3+0.5)),0):** This array formula checks for 1 (TRUE) by looking at data[Amount] between C3-0.5 and C3+0.5

The formula has two Boolean arrays multiplied and it returns a bunch of 1s & 0s.

MATCH then picks up the first such amount.

**Inner IFERROR(MATCH(…), MATCH(…)): **This acts like a fail-safe switch. If there is no exact match (first one), then lenient match (second one) will be used.

**Outer IFERROR(): **If no matches are found (exact or lenient) then “Not found” will be printed.

As this is an array formula, you need to press CTRL+Shift+Enter to get the result.

Related material – read these if you have questions about the formula techniques used above:

There are few more variations to this technique. Let’s review them.

Note: all of these are array formulas, so press CTRL+Shift+Enter.

We lookup just the whole number portion of the value to find match.

**Formula: **=INDEX(data[Client], MATCH(G7, INT(data[Amount]),0))

**Notes on how it works:**

- INT() turns data[Amount] column to whole numbers.
- We then lookup the amount (G7) and return the match

**Formula:** =INDEX(data[Client], MATCH(1, (data[Amount]>=G8)*(LEFT(data[Client],1)=”S”),0))

- We use a different Boolean structure with >= and LEFT() formulas. The output will be a bunch of 1s & 0s.
- INDEX+MATCH for find the first such value (G8)

This is interesting. We use MIN & ABS to find closest amount to input value (G10) and return the client’s name.

**Formula: **=INDEX(data[Client], MATCH(MIN(ABS(data[Amount]-G10)), ABS(data[Amount]-G10),0))

- ABS(data[Amount]-G10) gives a bunch of absolute (positive) values. The smallest of these will closest to G10.
- MIN() finds the smallest value
- MATCH looks up the minimum value from ABS(data[Amount]-G10)
- INDEX gives corresponding client’s name

**Click here to download the example workbook**. The file contains sample data, several examples of these techniques and additional resources to learn. Give it a go.

Lookups are an essential part of any data analysis work you do in Excel. Pick up some nifty tricks from these links.

- Basics:
- Advanced:

Have some lookup stories to tell? I am listening. Please post them in comments.

The post Lenient lookup [Advanced Formula Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post My top 5 tips for designing beautiful Power BI reports appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>*This post is part of our Power Mondays series, where every Monday I discuss Power BI, Power Query and Power Pivot. *

A good design should have balance and perfect alignment. Do you know that Power BI has alignment and distribution tools? Just select all the visuals you need to align (CTRL + Click on each visual) and use Format Ribbon to adjust their left / top / mid whatever else you fancy.

When you multi-select visuals (using CTRL+Click), you can also adjust their properties in one shot. After multi-selecting, just head to properties area and adjust what you want. In *most cases *your new settings will apply to all visuals.

*Pro tip: use this to make all visuals same size.*

Let’s be frank. The default color scheme of Power BI is pretty boring. It looks like someone washed your report in a front-loaded washing machine with cheap detergent.

The fix is rather simple. Just import a peppy, colorful theme and your visuals will don fresh coat of paint and look wow. Use the Switch theme from Home ribbon to import new theme to your report.

Note: When importing themes, pay attention to usability and contrast between colors. Try a few and pick the one that looks good and conveys better.

You can download free themes from Microsoft Power BI theme gallery. If you are adventurous, you can even make themes from the nifty Power BI theme-maker

Over time, Power BI format options for visuals has grown like a beautiful but tangled shrub in spring. On top, there is an ocassional re-org, so your favorite property to adjust *something *is now suddenly in a new group. With monthly updates, it all gets very hard to keep pace of these changes and new features.

Worry not. Just google the visual format properties, using the Search box. See this simple demo.

Power BI is like a fancy restaurant with 100s of main courses (charts, slicers, tables etc.) but only one type of side course of vertically sliced & deep fried potatoes (shapes). How else can you explain the mammoth variety of visuals and puny selection of shapes? Oh you want a shape? What is it – a rectangle, oval, triangle, line or arrow? What you want dodecagon? Go make one with XY plot you crazy nerd.

But you can creatively combine images, text boxes and shapes to get pretty much what you want. So to overcome the design limitations of the visuals, use images + shapes often.

Good design sense is a valuable skill. While there is some art in it, most of it can be learned. Take inspiration from giants. Like that fancy report your colleague in Payroll made? Take her out to a coffee and find out how it was put together. If you have no one else to turn to, check out the Power BI data stories gallery by Microsoft. Some of the showcased ones are pretty neat and inspiring. Copy the ideas to your own work and shine.