Power Query preview shows 1000 rows only 200 load

[jdbuchanan71]

I am pulling data using the new Amazon Open Search connector and when I am previewing the data in Power Query it shows 1000 rows in the preview.

I have even copied the date column from the preview into excel and I get 998 unique entries so I know the preview is pulling more that 200 rows but when I close and load, only 200 rows are loaded to the model.

I'm not doing many transformations so the query is just this.

let
    Source = AmazonOpenSearchService.Contents("https://search-**redacted**.amazonaws.com", 443, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Item", "Data"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Item] = "cwl-vbapi-2022.03"),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"date", "message"}, {"date", "message"})
in
    #"Expanded Data"

Has anyone else run into this behaviour and worked out a fix?

[Pascal150396]

@v-chenwuz-msft , @amitchandak 

@jdbuchanan71 

Is there any solution to that?
I have the same issue with Open search using either ODBC (open search connection), Amazon Open Search connector (Beta), or OpenSearch Project (Beta).

With ElasticSearch using ODBC it works just fine.

I got no restrictions in data. It shows in the preview 999+ and if I scroll down there is defenitely more then 200 rows that are only loaded into the report.
 

[jdbuchanan71]

@v-chenwuz-msft

All that step does is select a single file from the Amazon Open Search repository.  Then the next step expands that file into the 1000+ rows of data.
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"date", "message"}, {"date", "message"})
Then I close and load and only 200 rows are returned.

[v-chenwuz-msft]

Hi @jdbuchanan71  , 

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Item] = "cwl-vbapi-2022.03"),
This step only keep the rows which item is "cwl-vbapi-2022.03".

Maybe you can try only keep the first and the second steps, then load the data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.