Data Handling

Home/Data Handling

Power BI Data Sources for Estimating Market Share

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 [...]

2017-10-29T11:01:32+00:00 Data Handling, Power BI|

Get & Transform to the rescue!

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 [...]

Data is the oil of the 21st Century – Analytics is the engine

  Data is the oil of the 21st Century - Analytics is the engine “Data … the next natural resource” (Virginia Rometty, CEO, IBM, 2013) Oil - the commodity that got us here Current generations might be forgiven for being blasé about the mega role of oil in our daily lives. For most of us, [...]

Power Query Data Cleansing – solving all sorts of Excel issues

Power Query Data Cleansing - solving all sorts of Excel issues by Wyn Hopkins   Some may see Power Query simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop). Having spent 25 [...]

Introducing Power Pivot – Power Query (Get & Transform) – Video Introduction Part 3

Introducing Power Pivot  In this video I introduce Power Pivot and how you can use it to join multiple tables of data together and display your data in a Fiscal Year format. In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data [...]

Power Query (Get & Transform) – Video Introduction Part 2

Power Query (Get & Transform) - Part 2 In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel. In this 6 minute video I follow on from that introduction and add an extra transformation step to get the dates I need. Once [...]

Power Query (Get & Transform) – Video Introduction Part 1

Power Query (Get & Transform) Part 1 Power Query is amazing at linking to data sources and cleansing / transforming that data as it's loaded into Excel (or Power BI Desktop). Watch this 4 minute video and see how you can consolidate all of the files from a folder instantly using Power Query. Build the transformation steps [...]

Excel Dependent Drop Downs

  Dependent Drop Down Box   How to do one of those trickier tasks in Excel: set up a drop down box that is dependent on the result of another drop down box. The scenario If coffee is selected from drop down A then I want a list of coffee types to appear in drop down [...]

Easy Cumulative totals in Tables

Easy Cumulative totals in Tables The single greatest advancement in Excel in the last 10 years was the introduction of Tables.  Yet although Tables were introduced in Excel 2007 there is still a huge number (I'd even say the majority) of Excel users that don't know how to create tables or understand what they do. This [...]

Master INDEX MATCH in 60 seconds

INDEX MATCH is great but GETMATCH would be better I've written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP. However, it is a trickier formula and therefore not as widely used, so I've added a new formula suggestion to the Excel [...]

Fix MYOB Account Numbers Exported to Excel

Fix MYOB Account Numbers Exported to Excel It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won't find them.  Here's how you can fix MYOB account numbers. The Cause When MYOB [...]

2017-03-13T14:49:44+00:00 Data Handling, Functions|

58 Crazy Excel Stories (that are probably happening in your company right now)!

58 Crazy Excel Stories (that are probably happening in your company right now)! Crazy things happen when you mix untrained users with Excel. It’s not really rocket science.  Crazy things would happen with any complex system if you don’t train people properly. But many companies simply assume that everyone knows Excel, so they [...]