Failing to processs 500 rows with interdependent query filtering

[ajit_singh]

Trying to load 500 rows of csv (web url) with interdependent query filtering. Works till 200 rows but hangs on 500 rows. Here is the complete #PowerQuery code in GitHub gist url:

https://bit.ly/3SRJOQz

Power Query in Excel - Microsoft Excel 2021 (Version 2209 Build 16.0.15629.20152) 64-bit.

Failed in latest version of Power BI desktop as well.

[wdx223_Daniel]

= Table.ReplaceValue(Table.Distinct(Table.Combine(List.Transform({"50","100","200","500"},each Table.PromoteHeaders(Csv.Document(Web.Contents("https://archives.nseindia.com/content/indices/ind_nifty"&_&"list.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])))),"Symbol"),"","",(x,y,z)=>Text.Replace(Text.Replace(x,"-","_"),"&","_"),{"Symbol"})

[ajit_singh]

Thanks for the effort. Each csv file contains the previous csv file rows. The requirement is that only the distinct rows belonging to that csv file to be retained and the previous file rows needs to be filtered out.

Hence, I need 4 separate queries as below:

NIFTY_IDX_50 : All 50 rows
NIFTY_IDX_100 : All 100 rows except NIFTY_IDX_50 (50 rows)
NIFTY_IDX_200 : All 200 rows except NIFTY_IDX_50 & NIFTY_IDX_100 (100 rows)
NIFTY_IDX_500 : All 500 rows except NIFTY_IDX_50 & NIFTY_IDX_100 & NIFTY_IDX_200 (300 rows)

Till NIFTY_IDX_200, the query works fine, on the NIFTY_IDX_500 query, Power Query hangs for a long time and I need to cancel and close unsuccessfully.