Loading...

Follow Access Analytic Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


3 Easy Steps to Manage your Data Fields Want to control which data fields to keep in Power Query when removing other columns?

When using the ‘Remove Other Columns’ transformation in Power Query (‘Get & Transform’ in Excel 2016+) the query editor hard-codes the remaining column names in the Advanced Editor. This is fine if your database structure is supposed to remain static. However, if you wish to add additional field at some later point, you would have to do it manually in the ‘Advanced Editor’, which may be inconvenient. In this situation, it would help having an external list of field names, which you can edit and let the query keep only those appearing on the list.

Below are 3 easy steps on how you can manage your data fields in an external list:

To start with, let’s look at the following table (named tblSource) into Power Query, remove all the fields apart from “Full Name” and “Address”.

This would generate the following line in the Advanced Editor:

 #”Removed Other Columns” = Table.SelectColumns(Source,{“Full Name”, “Address”})

The field names {“Full Name”, “Address”} in the ‘curly brackets’ is a ‘list’ for the purposes of Power Query. We will have to replace it with a variable.

Now, let’s follow the steps to make the fields flexible:

Step 1. Create a table in an Excel sheet as shown below, and name it as ‘tblFieldsToKeep’.

Our table contains an extra field for “Phone Number”, which we wish to add to the final query result. 

Step 2. Copy the following line into the clipboard and paste into your source table query before the ‘Removed Other Columns’ line.

FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep]

Here we have read the table contents into Power Query and converted the column “Fields to Keep” into type ‘list’. The list is stored in the variable ‘FieldsToKeep’.

Step 3. Replace the field names in the ‘Removed Other Columns’ line with the variable, so the code looks as follows:

#"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)

At this point, our job is done. The new ‘Phone Number’ column will appear in the resulting table. If you add an extra field name to the list, it will appear after the query is refreshed (off-course, as long as it exists in the source data).

Here is how the resulting query will look:

let
   Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
   FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep],
   #"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)
in
   #"Removed Other Columns"

You may ask, though, what if you had to assign a specific type to each of the fields? The resulting line in the advanced editor would also contain hard coded values looking as follows:

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Full Name", type text}, {"Address", type text}, {"Phone Number", type text}})
What is Power Query?

Want to find out more about Power Query? Click here

The post 3 Easy Steps to Manage your Data Fields appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Access Analytic Blog by Jackie Meddows-taylor - 3M ago
What are Excel’s 3 Best Kept Secrets?

Excel has seen great advancement in the last 10 years and every user who spends their days re-organising data can benefit hugely from Excel’s 3 best kept secrets.

1. Power Query

If you or members of your team use Excel a lot then you are missing out massively if you don’t find out about Power Query.

The efficiency gains are staggering.

No more copy paste between Excel data dumps, no more hours cleaning up messy CSV’s or applying complex VBA.  Seriously, it’s amazing.

What is Power Query?

Want to find out more about Power Query? Click here

2. Power Pivot

Handle Millions of VLOOKUP style relationships in seconds

Load 100’s of millions of records into Excel.

Add massively useful formula to Pivot Tables such as Year To Date v Full Year Budget.  Actuals this year v Last Year, YoY Growth % etc.

What is Power Pivot?

Want to find out more about Power Pivot? Click here

3. Last but by no means least…the simple Table (Ctrl+T)

I still find only 20-30% of Excel users have used Tables . They are one of the 3 best things to happen to Excel in the last 20 years!

More Information

If you are interested in training courses in Excel and Power BI please click here

In-house Courses

If these dates don’t suit you, why not just run a course in your office.

[contact-form-7]

The post Excel’s 3 Best Kept Secrets appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

3 Signs your Company’s Management Reporting Needs a Massive Overhaul?

How much time does your organisation spend each month preparing budget and management reports?

How much time does management spend trying to understand these reports?

How much money does your organisation spend on reporting solutions?

Do any of the following reporting problems sound familiar to you?

Your reports are repetitive, error-prone and time-consuming to produce 

  • Creating each report is a long, manual process involving downloading information from multiple systems, copying and pasting values into Excel, formula troubleshooting, and endless re-formatting to make everything look nice.
  • All steps have to be repeated each month or time the report is due.

Your reports aren’t necessarily relevant to each user’s needs

  • You may have produced a report containing lots of financial information and then explain the report to a manager who isn’t responsible for financial KPIs.

Your reports don’t contain enough specific information

  • Significant trends in reports may be hard to notice, especially if the reports don’t contain chart visuals.
  • Even if chart visuals are included in the report and show a large trend downwards or upwards, your end user needs to know the specific causes of the trend! Static chart visuals won’t tell you this information.

How do businesses solve these reporting problems?

If you need quick, specific insights from your data to help drive better decision making in your business, you should strongly consider using Power BI as the solution for your reporting needs.

Here’s how Power BI can solve these three major problems:

Solution #1: Reports that automatically refresh based on user preferences

Power BI can connect to virtually any data source, eliminating the need to copy and paste information from different sources.

Once a Power BI report is created, there’s no need to re-create the report next month by reloading, re-cleaning and re-transforming a new set of data – just click refresh (or setup auto-refresh) to update your reports!

Power BI reports are also easily and securely shared with others in your organisation via PowerBI.com, which means no need to email spreadsheets back and forth and keep track of version control anymore!

Solution #2: Relevant, customisable reporting to satisfy different user needs

Power BI reports can be customised so that only certain data appears in the report for Manager A, Manager B, and so on, which eliminates the need to create different reports for different users of the same data set.

Once a report has been published to PowerBI.com, each report user can create their own interactive visualisations with the data via PowerBI.com, and pin these visualisations to dashboards for ease of use.

There’s also no need to worry about users accidentally changing a formula, as calculations are all set-up by the data model creator before the model is published to PowerBI.com.

Solution #3: Stunning, interactive visualisations that provide specific, relevant information

The AMAZING, interactive Power BI visualisations quickly help users identify trends in their data that need attention. Users can then click on a trend in a chart to filter other charts on the same page to identify the specific information explaining that trend.

One report containing 8-10 Power BI visualisations is like having over 100 reports all in one page! Why don’t you try and experience this for yourself? Take a look at a report in action.

At Access Analytic, we are passionate about using Power BI to transform data and create relevant, reliable and AMAZING solutions for clients. Why not engage us today to find out more?

The post Management Reporting in Need of an Overhaul? appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Dynamic Data Validation with Tables in Excel

Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong?

The answer to these common questions may surprise you.

Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” in a cell, “United  States of America ” (extra space after “United” and “America”) in another cell, and then wishes to use “United States of America” as a formula criteria, functions such as SUMIFS and VLOOKUP won’t work properly as “United States of America” was not entered consistently throughout each area of the workbook.

A great way of preventing this problem is to restrict the values that can be inputted into a cell via Data Validation. For example, if your company is currently selling to Australia, Thailand, New Zealand and China, you can enter these values as a list into one section of your workbook, and then use Data Validation to prevent misspelled and other variants of these country names from being entered into a cell.

Data Validation is a great tool, but what happens next month if your company starts selling to Indonesia and Russia? How do you automatically extend data validation into subsequent rows of your data entry table to avoid errors? The best way to solve this problem is with dynamic ranges and tables.

Here’s how to do this (file available via the link below to follow along):

Dynamic Data Validation – Excel

Step 1: Transform Your Data into Tables

If you’ve never used the Excel table feature before, you’re missing out! Excel tables are essential for dealing with large, complicated spreadsheets and help tremendously when dealing with Excel add-ins such as Power Query and Power Pivot.

Begin by selecting your data set (Ctrl + A) and then press Ctrl + T to turn the data into an Excel table. Then click on the Table Name box and give your table a sensible name with no spaces i.e. tblSalesData (“tbl” for Table). Repeat this process for your data validation list.

Step 2: Create a Defined Name

Go to your data validation table and highlight the column that will contain your data validation values (Ctrl + Spacebar). Go to Formulas/Name Manager or Ctrl + F3 to open the Name Manager.

Click the “New” button. Enter an appropriate name, such as ddCountries (dd for “dropdown”). Note that the “Refers to:” box is referring to every item in the data validation column with table formula nomenclature (table name followed by column name in square brackets). Click OK.

Step 3: Add Data Validation

Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu.

In the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box. Click OK twice to return to the main screen.

Now every cell in the column will contain data validation that is restricted to the values in the “Data Validation List” column. If you add another row to the Data Validation table, this will automatically appear in the drop down menus you have just created. New rows added to the revenue table will also include data validation for these values by default.

For additional fun, you can add additional tables with data validation to cycle among formula values.

The post Dynamic Data Validation with Tables in Excel appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Power BI data sources can be almost any file, database or web service.

Here’s an innovative way to use this amazing connectivity to estimate your market share using free information sources from the web.

Facebook tells me my Market Size

If you’ve ever placed an ad on Facebook, you’ll know the incredible range of characteristics it lets you specify when setting your target audience:

  • Location: country, region, city, suburb and within x km (or miles) of this.
  • Age range: max & min.
  • Gender.
  • Relationship status (it’s complicated!)
  • Interests.
  • Life events.
  • etc

This is fantastic for advertisers as they can target their ads to just the right people who are interested in whatever it is they’re selling.

Maybe this article is why I’m now seeing so many ads about advertising on Facebook!

To help you decide how much money you want to spend with Facebook, it provides an incredibly useful number, just before you click “Place ad”:

the Potential Reach estimate.

This can be one of your Power BI data sources!

If I know how big my potential market is, I can easily combine this with customer data from my CRM and estimate my market share.

Power BI Data Sources for Digital Demography

There is now an emerging area of study around this area, called “Digital Demography“, being pioneered by Bogdan State (Stanford) and Ingmar Weber (Qatar Computing Research Center).

The idea behind it is simple: we tell online services like Google, Facebook, LinkedIn, Twitter and our e-mail servers virtually everything there is to know about us.

This provides demographers and marketers with an online, ongoing, constantly updated census that tells us all about vast populations of people.

Rather than taking a census once every 4 years (as is done in Australia), then waiting for another year for the results to be collated and released, the online world provides a snapshot of this data immediately, whenever we require it.

All we need to do is tap into this!

Facebook

Facebook very kindly provides us with access to its free Marketing API.

With a little coding, this can be used as one of your Power BI data sources to automatically provide a constantly updated estimate of your specific target market size and market share.

Facebook won’t provide you with details of who the people are of course.  You need to pay Facebook some cash to advertise to your target market. However, Facebook will tell you how many people match your criteria … for free!

You could use this in cases such as:

  • Monitoring how your product or service is going in the market
  • Researching locations to open a new store by checking how many people within a radius of 5km share a particular interest
  • Checking how many people in Perth are interested in holidays in Europe (for airlines, tourism agencies etc)
  • Monitoring interest in a competitor within your target market

Unfortunately, Facebook’s API only provides your market share at the time you do the query.  It doesn’t tell you how this changes over time.  However, we can also do this via Power BI by storing the results automatically each day.

The following example URL string returns JSON code which Power BI can read.  It counts how many people there are on Facebook in the US, aged 25 years and over, and located within San Francisco (city code 2430536).  Just URLDecode the targeting_spec below to see how this works.

https://graph.facebook.com/v2.8/act_/reachestimate?currency=USD&optimize_for=OFFSITE_CONVERSIONS&access_token=&targeting_spec=%7B%22age_min%22%3A25%2C%22geo_locations%22%3A%7B%22cities%22%3A%5B%7B%22key%22%3A2430536%7D%5D%7D%7D

Google Trends

Google also provides an incredibly useful free tool for researching search trends and topics called Google Trends.

You can use this to check the relative search popularity for keywords or topics you’re interested in.  You can even compare multiple search terms to each other to see how popular each one is compared to the other.

You can then export all the data to CSV manually if you’re signed into Google.

The popularity of the “Power BI” search term over the last 5 years:

A much better method is to access the raw data directly, using Google Trends as one of your Power BI data sources.

Example of “Power BI” vs “Power Query” search terms (be warned: you’ll need to do a fair bit of data clean-up, however!)

Like some help?

If you don’t want to learn all the technical ins and outs of making this work, just leave your details below and we’d be happy to help.

[contact-form-7]

The post Power BI Data Sources for Estimating Market Share appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
How to…CSVs to Power BI Dashboard in 10 minutes!
[contact-form-7]

If you’ve ever clicked “Export to CSV” then we are kindred spirits.

In a previous life as analyst and accountant, I have been an unwilling follower of the export, copy paste, filter, remove top 2 rows, transpose mantra.

In recent years a lot of that pain was taken away as my knowledge of VBA (Macros) grew. But that knowledge was very much an internal skill that was too difficult to share with others.

Then along comes Power BI and everything changes.

Power BI is allowing more people to do amazing things with data that they couldn’t do before.

Fantasy Land

In the magical fantasy land of integrated reporting, we go into one system click on the report we want and we are done.

That place does not yet exist.

Every company I have worked at, whether there are 3,000 staff or 3 staff, is still exporting to CSV (or maybe exporting to Excel).

As Rob Collie says, the 3rd most popular button in any ERP system is Export to Excel (after OK and Cancel).

Power BI is making a difference

In the “old” days (i.e. 6 months ago!) we used to have to open up these exported files, copy the data then clean it up and paste it next to last month’s data. Then we’d create some reports and email them out to various stakeholders.

The process would take several hours at best or even a day or more, then we’d do it all again next month.

If this sounds familiar then you should want to watch the following video when I tell you that you can automate the entire process in Power BI.

The video is 10 minutes long, I hope that doesn’t put you off, since watching this may lead you down a path that saves you hours every month for the rest of your working life.

If you know all about PowerBI then this isn’t the video for you. You’ll already be sitting back having a coffee while your reports are refreshing automatically.

Data Hell to Reporting Heaven - YouTube

Next Steps:

To work on the CSV files get your free download here

Power BI is free to a large extent, and you can do a huge amount without any cost.

Download Power BI Desktop here

If you’d like to know more about Power BI look at this demo Power BI.com overview demo (video)

or check out our blog https://accessanalytic.com.au/blog

The post How to…CSVs to Power BI Dashboard in 10 minutes! appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Access Analytic Blog by Jackie Meddows-taylor - 7M ago

Get & Transform to the rescue!

Is it a bird? Is it a plane? No it’s way better than that, it’s Excel!

Get & Transform, also known as Power Query, gives Excel users super powers.

Here’s the latest scenario where it has helped out and also is an opportunity for me to demonstrate a method for referring to an item in the next row of data in the Power Query editor.

In Excel if you want to refer to the next row you just do =A2 or =B3 etc. However, you can’t do that in Power Query (sorry Get & Transform). But there is a way…..

This is the Scenario:

Crappy source data, in a terrible structure for analysis…

This is the Aim:

Nice clean data

This is the Method:

In a new workbook

Data > Get & Transform > New Query > From Workbook

Browse for the Excel dump with the crappy data in it, and pull that data into Get & Transform

Filter out the blanks and nulls

Referring to the next row

As you can see, the $ value we want is on the row after each name and address line

I want to pull that value up to be on the same row as the name and address line.

There are many approaches to this, here’s the 2 step process I used:

Step 1: I added an Index Column

Step 2: I added a Custom Column and typed this line of code

#”Added Index”[Column1]{[Index]}

(note [Column1] is just the column name I’m referring to, and #”Added Index” is simply the name of the previous step)

So now we have brought the item from the row below up onto the same row

From then on it’s plain sailing, just removing every 2nd row using the remove rows > alternate rows button

Splitting out the Totals: $ (using the split column functionality and using $ as a delimiter)

And finally splitting out the Description column, which happens to be separated by Line Feeds (Line Feeds are the sort of things that appear when you type in Excel and use Alt+Enter to start a new line)

Load to Excel and you’re done!

Just update / overwrite the source file with the latest crappy data and simply Right Click Refresh on your green table and awesome clean data is instantly available!

Get & Transform is Super!

The post Get & Transform to the rescue! appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
[contact-form-7]

“I Never Knew Excel Could do THAT!”

Spreadsheets have come a long way since they were invented in 1979!

They’re not the answer to everything … but they may well be the answer to more than you think because this is what we hear all the time:

“I Never Knew Excel Could do THAT!”

There are 3 classic mistakes organisations constantly make when they’re thinking about spreadsheets:

Mistake 1: “Everyone knows Excel”

Everyone can use Excel.  Even your mum can type numbers into boxes!  It’s easy … right?

But because everyone has it and can use it in some capacity, virtually everyone is self-taught, their knowledge is limited and they usually develop bad habits.

This leads to disorganised spreadsheets that are difficult to use, error-prone and inefficient and spreadsheets get a bad name.

The way most companies try to address this is by sending staff on a one-day course.

However, this has limited usefulness because it dumps a lot of information on staff in a very short space of time and doesn’t provide much opportunity to apply the new knowledge.

Furthermore, once the course is over, staff are left on their own to figure out how to apply what they’ve learned to what they do on a daily basis.

This is the first reason why people say “I Never Knew Excel Could do THAT!”

Mistake 2: “It’s just a Spreadsheet”

The greatest trick Microsoft ever pulled was convincing the world that Excel is just a spreadsheet!

“Excel is just a spreadsheet” so it’s not important … right?

Companies that adopt this view rarely take an objective, expert look at the spreadsheets in daily use to see what they’re doing or how they’re being used.  Yet there isn’t a company alive today that doesn’t depend heavily on spreadsheets to run their business!

If spreadsheets aren’t important, how come they run your business?

They’re only important when there’s an issue.  Then they’re REALLY important!

Maybe your staff should be saying “I Never Knew Excel Could do THAT!” a little less often?

Mistake 3: “You don’t need any Controls”

Spreadsheets are normally outside IT’s control.  All IT does is keep Excel running, install new versions and perhaps provide some technical support when users get out of their depth (e.g. connecting to a database).

This is both a strength, because users can do stuff for themselves (the main reason spreadsheets have been so successful), and a weakness, since they are uncontrolled and typical development standards and methods aren’t applied.

As a result of their misuse, some consider spreadsheets to be an evil virus that should be eradicated from the enterprise!

It’s cute when someone expresses the goal of eradicating spreadsheets … it will never happen!  They’re so useful, no company can live without them!

While we agree 100% that spreadsheets aren’t the answer to everything, companies shouldn’t throw the baby out with the bathwater.

Instead of trying to eradicate spreadsheets, companies would be far better off redirecting their efforts towards utilising spreadsheets properly.  This would achieve a far greater return, plus they’d have a much higher chance of success and maybe people would say “I Never Knew Excel Could do THAT!” a little less often.

PDF | Download the full report

to find out:

  • Why Excel is the most under-utilized software in the world
  • How Excel has more potential to benefit your business than you realise
  • 6 proven ways that will unlock Excel’s massive potential in your organisation

PDF | Download the full report

The post I Never Knew Excel Could do THAT! appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Training 100 Chevron staff in modern Excel and Power BI

I am halfway through delivering training in Power Query, Power Pivot and Power BI to 100 Chevron staff.

I love sharing my knowledge on this topic and seeing how people suddenly see what’s possible with Modern Excel.

You can see attendees’ eyes look up and to the left as the problem-solving part of their brains starts to process how they can apply these new found tools and skills. It’s exciting and rewarding!

I’m looking forward to sharing this knowledge with the next group of attendees and hopefully many more groups after that.

If you’d like to find out a bit more about Power BI you can read more here.

Check out some of the course feedback!

If you’d like to run in-house training or attend a public training course just let us know here!

Courses are run in…

Perth | Singapore | Hong Kong | Malaysia | Australia Wide | World Wide |

The post Training 100 Chevron staff in modern Excel and Power BI appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Access Analytic Blog by Jackie Meddows-taylor - 7M ago

What is Power BI.com?

This is the next in a series of 4-5 minute videos introducing the Power BI suite consisting of Excel Power Pivot & Power Query, Power BI desktop and Power BI.com

Here’s a link to my previous article on Power Pivot in Excel.

This time it’s Power BI.com and a short introduction into the sort of things it can do

In a nutshell it’s a place to host your reports and dashboards so that others can interact with them. And that’s just the start. Take a look, you can even play with the report on our website after watching the video.

Power BI - what is it? - YouTube

In the next video I’ll demonstrate how to build one of these reports from start to finish in Power BI Desktop and then publish it to Power BI.com

Please leave some feedback, like and share.

For more articles… https://accessanalytic.com.au/blog/

The post Introducing Power BI.com – Video appeared first on Access Analytic.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview