More Power Query–using the fabulous .csv-file

There are lots of fancy data sources that Power Query can grab data from, like the Web, Databases, Excel

 

image

and “Other Sources”

image

but the day to day, bread and butter problem solver is most likely to be .csv since almost every self respecting application around has the option to export .csv-files.

I’ll give you the theory behind a practical example, but I can’t give you the facts since they belong to an operating company. This is a global corporation operating on a 5-4-4 fiscal calendar, and for those of you who have never encountered this I can tell you it causes a lot of headaches at year end, at least in Sweden. The challenge being that the fiscal year will end on a Friday, not on the last day of a month. Inevitably this means that you will have to add or deduct days in order to create an annual report (for tax purposes) that conforms to local (Swedish) regulations. Manually identifying transactions, building a bridge in Excel and finally consolidating the whole mess is very time consuming. I have participated in this exercise when it has taken days to get it right. It also requires foolproof storing of the previous bridge since the problem exists at both ends of a fiscal year.

Power Query will help you immensely in this type of situation in a very efficient manner

1. Export your transaction list (from SAP or whatever you use) for the month in question to a .csv-file

2. Open Power Query and select the .csv-file as your source

3. Filter to the dates you need to add or subtract

4. Filter on the document types you have to take into account

5. Load the Query results to your workbook

6. Finish building your bridge

I estimate that already the first time out you will get this done in less than an hour, and as your expertise in Power Query grows you will spend less and less time on this type of exercise. You will also find that you no longer have to wait for the books to be closed to start working on your bridge – just refresh your query on a daily bases until the books are closed and you will be light years ahead of the game!

One small example – more to follow on the awesome journey into Power Query.

Advertisements

About powerpivotseth

As long as I learn, I live, and to that end I am now immersing myself in the new Microsoft Power BI applications available through Excel. I was quite good at plain vanilla Excel, but even so, I ended up in "No, that is not possible" dead ends at times. Then I discovered Power Pivot and all my previous dead ends opened up into veritable eight-lane interstate highways!
This entry was posted in Power BI, Power Query and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s