Loading...

Follow Access Analytic Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid
Access Analytic Blog by Jackie Meddows-taylor - 1d ago

Are you experiencing an issue when unpivotting Excel data?

Unfortunately as soon as you try to Unpivot a table of data containing a #DIV/0 or #/NA you get a very strange warning….

“The operation failed because the source databases does not exist, the source table does not exist, or because you do not have access to the data source.”

This is totally misleading, however the “More Details:” part of the message does tell you it’s related to a #DIV/0!.

Now, the best approach is to investigate the source file and fix the #DIV/0. However, for a number of reasons this is 1) highly time consuming, 2) the errors are known and the data isn’t required. You just need to convert the errors to 0.

So the simple approach would be to highlight all of the columns in Power Query and do a replace all errors. However, life is never that simple. If you don’t want your code to refer to any of the column names, if the column names are not consistent in the files you are consolidating – which is usually the case.

The solution can be found here…

https://stackoverflow.com/questions/39299331/replace-all-error-values-of-all-columns-after-importing-datas-while-keeping-the

and this is the 4 lines of code…

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    

 // Generate a list of all the column headings

    AllColHeadings = Table.ColumnNames(Source), 

    
    
 // Use List.Transform to create a list of the the column headings with a value of 0 against each one
 
   ColsAndReplacementVals = List.Transform( AllColHeadings, each {_, 0}),
    

    
 // Then use Table.ReplaceErrorValues using the original table (Source)  and  and the new list of columns and replacement values

    ReplaceErrors = Table.ReplaceErrorValues(Source, ColsAndReplacementVals)  



in
      ReplaceErrors

You can now happily unpivot your data without issue, and more importantly for, in this scenario, you don’t have to reference any of the columns in the code.

Want to learn more? Follow these useful links

Power Query articles | Power Query books

The post Remove all errors with Power Query appeared first on Access Analytic.

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

Microsoft UserVoice

We all want to make Excel and Power BI better. There are new features we’d love to see, and there are existing issues we’d like to fix. Microsoft are listening to users expressing their views via voting on the UserVoice sites.

Vote for the features you want

A number of experts (including our own Wyn Hopkins) have listed the features they’d like people to vote for in an interactive Power BI report.

Here’s how to navigate around the UserVoice dashboard

UserVoice - YouTube

UserVoice Dashboard

The post Vote for the Excel and Power BI features you want appeared first on Access Analytic.

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

Approximately 1.2 million businesses use MYOB products, this versatile accounting package has several offerings, but one thing they have in common is a less than amazing reporting function and integration with Excel.
In 2013 MYOB Introduced a set of Application Programming Interfaces (APIs) for several of its products, making it now possible to extract data from company files online and in real time.

MYOB APIs and Excel

Thanks to Excel’s native power query and power pivot functionality, your spreadsheets can now consume data directly from your MYOB Company files.

I recently had the opportunity of applying MYOB’s Account Right API Capability, to develop a set of business financial statements integrating over 10 separate MYOB company files.

This reporting tool now caters for all the financial reporting needs of the organisation. Not only has it saved hours/days of work every month, but it provides financial visibility and accuracy that was just not possible via the manual process. Best of all it does require any additional software or licenses just Excel.

Presently MYOB has released APIs for the following products:

What is an API?

An API is a set of commands, functions, protocols, and routines that can use to create software or interact with a systems or software. It provides developers with standard commands for performing common operations, eliminating the need for extensive code writing.

There are several types of APIs, the most common (and the ones used by MYOB) are referred to as Web APIs, otherwise known as Web Services these provide an interface for web applications, or applications that need to connect to each other via the Internet to communicate.

Web APIs adopt a Uniform Resource Identifier (URI) syntax, think of them as URLs with additional commands, which communicate with the host system.  These are referred to as a resource (location of information) and the API Call (which send instructions to the MYOB environment).

 Need some advice

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 MYOB and Excel/Power BI integration appeared first on Access Analytic.

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

Every Excel Power Pivot Model or Power BI Desktop file needs a Calendar.

With the addition of a Calendar to your Data Model you can start to do all sorts of useful analysis such sorting the data by Fiscal month and Fiscal Year or performing calculations such as TotalYTD, Year On Year Growth, Actual v Full Year Budget etc… this list goes on.

It’s a critical element.

There are several ways to create this Calendar, but the most flexible way is to use Power Query.

This does involve a little coding – using “M” – which is the language of Power Query.

Or, you’ll be glad to hear, just some simple copy pasting!

Once you’ve taken 2 minutes to set it up you can re-use it again and again.

Download a pre-built calendar

If you don’t want to read all about the “how to” below, you can simply download a prebuilt Power Query Calendar (in an Excel file) here

Then you can open that Excel file, show the query pane, and then right-click on the Calendar query , choose Copy and then paste it into your project (Excel or Power BI)

You can then easily change the Fiscal Year End, Start Date and End Date in the first 3 steps of the query.

Alternatively, here’s the Power Query “M” code I use for all of my projects to create a calendar automatically. You can create a blank query and copy and paste it into your advanced query editor.

let
   

    EndFiscalYearMonth = 6,   //set this as the last month number of your fiscal year : June = 6, July =7 etc


    StartDate= #date(2016,7,1),     // Change start date  #date(yyyy,m,d)   
    EndDate = #date(2018,6,30),  // Could change to DateTime.LocalNow() if you want to always show up to the current date


/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
   You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the // 
*/


    //TableName = Sales,    
    //DateColumnName = "Invoice Date",
    //StartDate = Record.Field (   Table.Min(TableName,DateColumnName)  ,DateColumnName), 
    //EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),


    
    DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), type number),
    #"Long Month Name" = Table.AddColumn(#"Inserted Month Number", "Month Long", each Date.MonthName([Date]), type text),
    #"Short Month Name" = Table.AddColumn(#"Long Month Name", "Month", each Text.Start([Month Long], 3), type text),
    #"Fiscal Month Number" = Table.AddColumn(#"Short Month Name", "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth),
    #"Changed Type1" = Table.TransformColumnTypes(#"Fiscal Month Number",{{"Fiscal Month Number", Int64.Type}}),
    #"Fiscal Year" = Table.AddColumn(#"Changed Type1", "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Calendar Year]+1 else [Calendar Year]),
    #"Changed Years to Text" = Table.TransformColumnTypes(#"Fiscal Year",{{"Fiscal Year", type text}, {"Calendar Year", type text}}),
    FYName = Table.AddColumn(#"Changed Years to Text", "FYName", each "FY"&Text.End([Fiscal Year],2))
in
    FYName

If you’re not familiar with M code then the // is a way of adding comments or “turning off” bits of code

I’ve set it up so that it can be used in a number of ways, but as it stands we have a StartDate of 1st July2016 and an end date of 30 June 2018.

However, you can amend the code to a dynamic End Date using DateTime.LocalNow()

Best to start with the 1st of your current or any prior fiscal year and end with the last day of your current or future fiscal year.

Dynamic Start and End Dates

Maybe you want the Start Date and End date to be dynamically derived from the earliest and latest date in a column in a specific table / tables.

In that case, you add a double slash // before the StartDate and EndDate in rows 2 and 3 then remove the // from the 4 lines below. Then replace the word Sales and Invoice Date with the relevant query (table) name and column name.

//TableName = Sales,    
//DateColumnName = "Invoice Date",
//StartDate = Record.Field (   Table.Min(TableName,"DateColumnName")  ,"DateColumnName"), 
//EndDate = Record.Field(Table.Max(TableName,"DateColumnName"),"DateColumnName"),

Want to learn more? Follow these useful links

Power Query articles | Power Query books

The post Download your own Power Query Calendar appeared first on Access Analytic.

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

Power Query is the greatest addition to Excel in 10 years, it’s amazing at extracting and transforming data ready for you to use in a report. In this two minute video we show you how easy it is to extract the data you need without using any formulas!

Power Query No Fomulas - YouTube

Find out more

Extract and clean your data using Microsoft Power Query. Plain Speaking: Power Query is the worlds’ greatest washing machine! Get all of your “dirty” data from any location, clean it up via a user friendly interface and then load it all neat and folded to a destination of your choice (Excel or Power Pivot).

Power Query articles | Power Query books

The post Free yourself from using formulas appeared first on Access Analytic.

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

This 3 minute video shows how easy it is to link to web page data with Excel.

In this scenario it’s connecting to the Reserve Bank of Australia exchange rates web page.

Get & Transform is built into Excel 2016.

It was previously known as Power Query and is still a free download for Excel 2013 / 2010.

Data From Web - YouTube

This identical functionality is available in PowerBI Desktop.

The URL if you want to give it a go yourself…

http://www.rba.gov.au/statistics/frequency/exchange-rates.html

Find out more

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

Download Power BI Desktop here

The post How to pull exchange rates from a web page into Excel or Power BI appeared first on Access Analytic.

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

Excel and Power BI - From the Microsoft Studios - YouTube

Excel and Power BI are amazing analysis and data visualisation tools. Knowing what’s possible is just the beginning.

You can see the possibilities in this video recently recorded at the Microsoft Studios in Seattle.

The speed of change and capabilities of Excel and Power BI are dizzying. The benefits of learning these technologies will save your business money and make your job easier.

Want to find out more?

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)

The post Excel and Power BI – From the Microsoft Studios appeared first on Access Analytic.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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 - 8M 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

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 month
Free Preview