Ideas for setting up financial data

Power Query Data Setup
1

Hi everyone, I am back and looking for some guidance on data set up again.
I am undertaking a project to migrate 120 airlines financial data into a format that is BI friendly so I can move to BI reporting from excel.

I am at phase 1 and I have been going through Financial Reporting /w BI in the structured learning. I have decided to reformat the data in excel so that the data entering BI via power query is as clean as possible.

I am looking to the forum for guidance as to what might be best practise.
First off I am working with an excel databook for 120 airlines and in this excel there are a number of key inputs:

  • annual operational
  • quarterly/semi annual operational
  • annual income statement
  • quarterly/semi annual income statement
  • annual balance sheet
  • quarterly/semi annual balance sheet
  • annual cash flow
  • quarterly/semi annual cash flow

Attached is an example of the excel sheet that is currently used to track the figures. I am trying to figure out the best way to set this data up. I am looking a couple of options

a) Have a separate tab for each of the bullet sections above

image
image869×819 15 KB

b) alternatively I thought it might be better to include all data in one tab and just use more column categories for example:

image
image946×867 15.7 KB

I think this set up is going to be one of the most important factors as I have to transform 120 spreadsheets into this so I really want to get it right if at all possible. Any guidance or help at this stage would be greatly appreciated.

Thanks all
3) Air Canada Financial Data Book Q3 2021 (inc IFRS 16).xlsx (906.2 KB)

2

Welcome back to the forum @Ronan :slight_smile:

While waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!

Bumping this post for more visibility.

3

Hi @Ronan - Looking at the inputs and sample provided, I will suggest to go with Option 2 as it will result in single fact table considering

  1. Other columns like Airline/Code/Type are same across different sheets.
  2. Data is not huge in Individual sheets, so that combined data in Single Table is in Millions.
  3. There are separate tables for Financial Template and report will make use of measures to get the desired value from single table.

Can also look to create separate lookup tables for Airlines Categories/Type etc to have a Fact Schema.

Thanks
Ankit J

4

Thank you Ankit, yes I have the lookup tables held outside of this and they are all straight forward rather it was these large fact tables that I was not so sure. I will have quite a lot of data when I build it all out.
Thank you very much for your input

5

It’s great to know that you are making progress with your query @Ronan.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

6

Hello @Ronan, just following up if the response from @Ankit help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Yes I understand this but I kept it open to just see if there were any alternative responses. I will close it today

8

Hi @Ronan, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.