Forum Discussion

ajit_singh's avatar
ajit_singh
Frequent Visitor
3 years ago

Failing to processs 500 rows with interdependent query filtering

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/3...
Need Help
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

= 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's avatar
ajit_singh
Frequent Visitor
3 years ago

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.