How to get data using Power Query

[GAURAV SEMWAL]

https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip

How to get data from this link using Power Query as per selection of date .
Earlier i was using that below given process but it is not working to this link.

 

[smozgur]

The link you provided contains a zipped CSV file, which cannot be opened in Power Query directly. Your earlier implementation was possible because the linked file was an Excel file that could be downloaded and read by Power Query.

You should either download and unzip the zip file or a much easier way would be to find out if the web service provides a CSV file download instead of a zip archive and then you can use the extracted CSV file with Csv.Document function (not Excel.Workbook).

Note: I edited your question's title and post to change the "all caps" as you requested.

 

[GAURAV SEMWAL]

The link you provided contains a zipped CSV file, which cannot be opened in Power Query directly. Your earlier implementation was possible because the linked file was an Excel file that could be downloaded and read by Power Query.

You should either download and unzip the zip file or a much easier way would be to find out if the web service provides a CSV file download instead of a zip archive and then you can use the extracted CSV file with Csv.Document function (not Excel.Workbook).

Note: I edited your question's title and post to change the "all caps" as you requested.

first of all thank you for change the format of my question.
and sir i am using this code to unzip the file

and after that getting this error.

 

[smozgur]

Good, you already implemented the download/unzip part.

The error you get says that the source data/table doesn't contain the column named CONTRACT_D. The best part of the Power Query editor, you can click on the APPLIED STEPS one by one, and see why the steps are failing.

Try that and start going backward from the last step. Basically, click on the Added Column step to see what it generates. You will see that there is no column named CONTRACT_D. That's the error that needs to be fixed because the last step is supposed to filter data where CONTRACT_D is equal to some specific names (see the code text box, or you can click on the Advanced Editor to see the whole M code). Perhaps the column name is changed, OR the source data is not the one that you expected to get.

 

[GAURAV SEMWAL]

Good, you already implemented the download/unzip part.

The error you get says that the source data/table doesn't contain the column named CONTRACT_D. The best part of the Power Query editor, you can click on the APPLIED STEPS one by one, and see why the steps are failing.

Try that and start going backward from the last step. Basically, click on the Added Column step to see what it generates. You will see that there is no column named CONTRACT_D. That's the error that needs to be fixed because the last step is supposed to filter data where CONTRACT_D is equal to some specific names (see the code text box, or you can click on the Advanced Editor to see the whole M code). Perhaps the column name is changed, OR the source data is not the one that you expected to get.

sir after following your steps i found that some unusual that header name is coloumn 1.2.3.....

 

[smozgur]

One more suggestion about posting questions. Although uploading images is possible in the MrExcel Message Board, helpers mostly like to get the codes in copiable format, so they can test/run the actual code to provide better help. It is not possible to do that when the code is posted as a screenshot and believe me, many helpers wouldn't spend time rewriting the code by looking at the image.

So, I also suggest posting the codes as text and using the corresponding BB Code tag buttons on the editor toolbar to wrap them. This way, they could be copied easily, and also much more readable. Again, just a suggestion.

 

[smozgur]

sir after following your steps i found that some unusual that header name is coloumn 1.2.3.....

I don't think you got the proper CSV file imported. Can you go to the previous steps as well and see what you have after the unzipping? Perhaps the unzip routine is not working well.

(See? If any helper reading your question had the M code in copiable format, I am sure they would test it to see what's wrong with it.)

 

[smozgur]

I can see that column names used in the Power Query code are not compatible with the actual CSV file column names. The following mini-sheet shows the column names and sample data, so you can compare which columns you need to process.

Book1
	A	B
1	INSTRUMENT	FUTIDX
2	SYMBOL	BANKNIFTY
3	EXPIRY_DT	10/27/2022
4	STRIKE_PR	0
5	OPTION_TYP	XX
6	OPEN	40493.3
7	HIGH	41030
8	LOW	40396.5
9	CLOSE	40788.3
10	SETTLE_PR	40788.3
11	CONTRACTS	183408
12	VAL_INLAKH	1867031.68
13	OPEN_INT	1406925
14	CHG_IN_OI	-112600
15	TIMESTAMP	10/21/2022
Sheet1

If the download/unzip routine works correctly, then you should also see the same tabular data when you click on the Imported CSV step.

 

[GAURAV SEMWAL]

sir i want in this format.

 

[smozgur]

As I explained - I don't think you have all the corresponding columns in the CSV file.
And again, click on the Imported CSV step and see the columns that you have. The previously used XLS file might have contained all the columns that you need so the previous implementation might have been built on that particular data structure, however, the current CSV file doesn't have all columns that in the requested format.