Log In
Register
Home
September 24, 2020
Offline
Hi Myanda,
My name is Punith & I'm from Chennai, India. I am new user to Power Query.
My purpose of using Power Query is extract financial data of public listed companies in India. For this purpose, I use "www.moneycontrol.com" to extract 10 year financial data (Balance Sheet, Profit/Loss & Cash Flow statement)
The power query option extracts two types of financial data (Standalone / Consolidated). The difference is that there difference in no of rows.
Moreover when I use power query, I am getting data in the below show format
The required format I am looking for from power query is,
Please refer sheet "04_Data_Extraction" & "05_Moneycontrol_Data" of the attached xl sheet for further calculation steps
My query is for two items.
1. When we extract "Standalone" or "Consolidated" financial data where there is difference in rows, would Power Query clear the old data and paste the new one.
2. Is it possible to format the table structure as shown above in Power Query.
Please let me know the work around on this query. Thanks in advance.
Regards,
Punith
See Answer
July 16, 2010
Offline
Hi Punith,
Welcome to our forum!
I'm a bit confused because you say that the data format you're getting from Power Query is as shown in your first image, but the data format you're actually getting with Power Query is on sheet 05_Moneycontrol_Data and it's nothing like that image. I presume the images you've attached represent an abstract version of how you think of the data you're getting out, but as I'm not familiar with this data or your objective, it means nothing to me because I can't relate it directly back to what Power Query is placing in 05_Moneycontrol_Data sheet.
Please rephrase your question so that it's is identical to the actual results in the file, that way I can follow what you're trying to do. It's best if you can create a mock-up of your desired result that directly relates back to the data in the file so I can follow the audit trail of how you're getting from A to B.
Mynda
September 24, 2020
Offline
Hi Mynda,
Thank you for your reply. The above shared image is an abstract version on how I am getting data from Power Query. Below is a snap shot of the zoomed out image of "05_Moneycontrol_Data". If you refer image "Capture111.JPG" and the data in the green cells it would of the same structure. My requirement is that when power query outputs the data can I get in the format as show in image "Capture112.JPG". Please refer cells "O1 to AW66" & "O68 to AW121" in "05_Moneycontrol_Data"
Regards,
Punith
July 16, 2010
Offline
Hi Punith,
I can't read that image, but I think it's given me just enough to follow what you're trying to do. In the attached file I've taken one of the queries and pasted it as values because I couldn't refresh your queries, as they were timing out.
You need to extract the Balance Sheet, P&L and Cash Flows into separate queries, then you need to separate them into two more queries, one for 2016-2020 and one for 2011-2015.
See file attached. Hopefully you can follow the steps to understand what I did as it's too much to explain here. If you're not able to follow it then I recommend you take my Power Query course.
Mynda
P.S. Next time please provide a simple example that's easy to follow rather than your whole file.
Answers Post
September 24, 2020
Offline
Hello Mynda,
First my apologies for sending the whole file. For that reason, I had sent a image on how my actual excel file would be.
Based your feedback, I had organised the query for 5 years period individually. By using merge option, I had merged the queries. This work around works. Below is a snapshot of the query structure. Mynda thank you suggestions. This has made my workflow easier.
Regards,
Punith
1 Guest(s)
