Forum

Downloading Multipl...
 
Notifications
Clear all

Downloading Multiple Web pages and Reorganizing in Power Query

6 Posts
2 Users
0 Reactions
73 Views
(@punith84)
Posts: 6
Active Member
Topic starter
 

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.

Capture_110.JPG

Moreover when I use power query, I am getting data in the below show format

Capture_111.JPG

The required format I am looking for from power query is,

Capture_112.JPG

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

 
Posted : 24/09/2020 11:15 am
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 24/09/2020 6:47 pm
(@punith84)
Posts: 6
Active Member
Topic starter
 

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"

 

Capture_113.JPG

 

Regards,

Punith

 
Posted : 25/09/2020 12:45 am
(@mynda)
Posts: 4761
Member Admin
 

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.

 
Posted : 25/09/2020 7:26 am
(@punith84)
Posts: 6
Active Member
Topic starter
 

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

Capture_128.JPG

 
Posted : 28/09/2020 10:37 pm
(@mynda)
Posts: 4761
Member Admin
 

Glad it was helpful, Punith.

 
Posted : 29/09/2020 12:08 am
Share: