Hi, I need some help with automating the importing information (in my case Financial Report)

[Alizz]

To begin with, I use FinnPro to get the financial statement as an Excel file, then I try to import the necessary details only into my Excel through Power BI. And my goal is that whenever I use a new report, I need to update the source file in Power BI instead of going through all the steps again because errors from the source file are different (given each company has a different start time, for example, some of them have reports from 2011 some have reports from 2002 etc)

I tried doing each step manually like removing colloums, removing rows, rounding off (cuz I want all the number round to 2 decimal places) but everytime I apply this to a new file (changing source) lots of them just break and I got to fix step by step and it take even longer than copy paste. 

Please help me fix the steps or any other solutions are appreciated.

Here is the raw data:
Link to the raw file in Google Drive 

For example here is the result I want (basically taking all financial data from a certain amount of previous year (for example 10 years from today)
Here is just the simplify version because it exceed the characters limit.

	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
All Financial DATA	13,018.93	15,457.99	16,731.88	18,673.83	20,307.43	20,559.76	24,721.57	29,665.73	36,109.91	31,560.38	35,935.88

[Alizz]

Hi MFelix, firstly thank you so much for your respond.

After watching the videos I would like the simplify my problem as follow: is there a way to only retain columns with headers > a certain value (2013) for example, or to delete all columns where headers < than 2013. 
Because in the videos Its only change the headers but in my case, the headers (year value) does not change but the number of columns changed. (For example, one report have data since 2010-2023, while the another have since 2015-2023). I just want to retain a fixed data from 2013-2023. (In case source data don't have enough e.g 2015-2023, then just skip the missing years or leave blank).
Hope it helps.

[MFelix · Accepted solution]

Hi @Alizz ,

If that is the case I would suggest for you to go with the approach I refer of making the unpivot other columns because then you can use do the specific filters you need.

Even more the unpivot will help you to get a better modelling to make the calculations has you need since having a column for each of the years will give you problems when building your visualizations and compariosions.

[MFelix]

Hi @Alizz ,

The request you are making will depend on the file you have, especially on the question about the names of the columns, that is where I think you may be hitting the several erros you are having. because if you are always rebuilding your source the name will vary according to the selection.

On the example you refer you have your information by year and the years keep changing so when yoy try and look for 2011 you can't find it and gives you the incorrect value.

For the specific example your are showing I would advise you to do select the first column and do a unpivot others that wouls give you 3 column one with the first row values and then one with years and another with the value for each year. Then clean out all the blank values.

However you need do a more intricate change please look at the links below with step by step guide of files that have columns with names being updated constantly.

https://www.youtube.com/watch?v=gJPA_Faotkk

https://www.youtube.com/watch?v=wSwXyfaXQgU