Data Warehousing, BI and Data Science

11 September 2020

Copy Paste Anchor Links (Page Jumps) from Microsoft Word to WordPress

Filed under: Data Warehousing — Vincent Rainardi @ 6:57 am

Problem: when copy-pasting a Word document to WordPress, hyperlinks are preserved but bookmarks are gone.

Solution:

  1. In Microsoft Word create them as hyperlinks
  2. Paste it to WordPress
  3. Change the hyperlinks to anchor links using Notepad
  4. Paste back to WordPress

Step by step:

Step 1. In Microsoft Word, create them as hyperlinks

  • In a blank Microsoft Word document, create this:

Both of the “Section 1” are normal hypelinks, with text = “Section1” (no space), address = “#section1” (no space with # prefix) like this:

  • Highlight it, control-K (Insert Hyperlink), type Address = #Section 1 (notice the hash in the front).
  • Create Section 2 and Section 3 the same way.

Step 2. Copy to WordPress

  • Control-A (select all)
  • Copy paste to WordPress visual editor.

Step 3. Replace the hyperlinks to anchor links in Notepad

  • Control-Shift-Alt-M (switch to Code Editor)
  • Control-A (select all)
  • Paste to Notepad (Notepad++ is better)
  • Control-H (replace), replace href with name, and remove the # as follows:
    find what: <a href=”#, replace with: <a name=”

Step 4. Paste back to WordPress

  • Control-A and paste back to WordPress code editor.
  • Click Update on the top right corner
  • View the page and test the hyperlink

10 September 2020

Turtle

Filed under: Data Warehousing — Vincent Rainardi @ 5:07 am

My son asked me to show graphics in programming and I thought it would be a good idea to show Turtle. In Python of course, as it is the most popular programming language, serving all kinds of communities not just AI. Turtle is a simple way to draw lines and circles. And it’s already built in Python, i.e. you don’t need to install anything.

To start with just type “import turtle”. This is to import the turtle library into python environment.

Then type “t = turtle.Turtle()”. This is to create a variable t so that we don’t have to type turtle.Turtle many many times. It opens a window like this with an arrow facing to the right. That arrow is the cursor. That window is our canvas, where we make our drawing.

To draw a line, type “t.forward(50)”. This is to move the arrow 50 pixel forward (to the right).

To change the same of the cursor, type “t.shape(“turtle”)”:

To draw a circle, type “t.circle(50)”:

To turn the turtle to the right 90 degrees, type “t.right(90)”:

And you can do “t.forward(50)” to draw a line again:

To lift the pen up so it won’t make any line, type “t.penup()”. Now we can move the turtle without drawing anything, for example: “t.setposition(0,-50)” like this:

(0,0) is where we began. It’s x,y so 0,-50 means the x is 0 and the y is -50 (50 down).

50,0 means x = 50 and y = 0, like below:

Remember that our pen is still up at the moment. To start drawing again, let’s put the pen down by typing “t.pendown()”. We can draw a circle again: “t.circle(25)”, like below left:

To undo what we did last, type “t.undo()”, like above right.

To fill with colour, type this:

  • t.fillcolor(“green”)
  • t.begin_fill()
  • t.circle(25)
  • t.end_fill()

And finally, to clear the screen, type: “t.clear()”.

As an example, to make a square, it is like below. We use a “for” loop.

for _ in range(4): {shift-enter}
  t.forward(25) {shift-enter}
  t.right(90) {enter 2x}

Another example: a filled hexagon.

t.fillcolor("green") {enter}
t.begin_fill(){enter}
for _ in range(6): {shift-enter}
   t.forward(25) {shift-enter}
   t.right(60) {enter 2x}
t.end_fill(){enter}

Have fun!

6 September 2020

Dynamic Difference in Power BI

Filed under: Data Warehousing — Vincent Rainardi @ 3:30 pm

If you are looking for how to do dynamic difference (dynamic delta) in Power BI, you’ve come to the right place. This article is about showing how to calculate the difference (say between actual and budget) dynamically or on-the-fly as you switch from product to product. This is done using the SelectedValue function.

Business Scenario

Here is the business scenario: you work in product development in a manufacturing company. You have many products developed in your product development pipeline, each have different costs for each component like this:

Report Layout

The Power BI report should look like this:

You need to be able to select a product (Product A in the above example) and the actual spend vs budget are displayed in a table, along with the difference.

So you are comparing the cost breakdown of a product to the budget. Another business scenario which is similar to this is comparing the performance attribution of a mutual fund to the benchmark. In this case instead of components, we have industry sectors.

Dynamic Difference

The point here is that the differences are calculated on-the-fly, depending on the user selection. That is the key Power BI functionality that I’d like to show you.

There is a function in Power BI called SELECTEDVALUE, which we need to use for this. We use it to find out which product is being selected.

How To Do It

So let’s create the Power BI report.

Step 1. First, in Excel, type this table, and load it in Power BI as Cost table:

Step 2. Create a calculated table to select distinct values from the Product column:

   Product = distinct(‘Cost'[Product])

Step 3. Use it for a slicer so we can select Product A, B and C (filter the Budget out):

Step 4. Create 3 measures: Cost_Budget, Cost_Actual and Cost_Differene as follows:

1. Cost for the budget:
Cost_Budget =
CALCULATE
( SUM(‘Cost'[Cost]),
  FILTER(‘Cost’, ‘Cost'[Product] = “Budget”)
)

2. Cost for the selected product:
Cost_Actual =
CALCULATE
( SUM(‘Cost'[Cost]),
  FILTER(‘Cost’, ‘Cost'[Product] = SELECTEDVALUE(Product[Product]))
)

3. The difference between the budget and actual:
Cost_Difference = [Cost_Budget] – [Cost_Actual]

Step 4. On the report create a table with Component, Budget, Product and Difference in the Values.

Now when we select Product A it will display the cost of Product A and calculate the difference on-the-fly.
And when we select Product B it will display the cost of Product B and calculate the difference on-the-fly.

Blog at WordPress.com.