cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
julhelp
Helper I
Helper I

Not getting the exact number of records

Hi,

 

my problem is, that i am not getting the exact number of records when i load it into my Excel Sheet.

 

It should return 25.429 records. But i am getting 26.000:

julhelp_0-1643116322111.png

There were also 26.000 records loaded:

julhelp_1-1643116432500.png

But actually I should just get 25.429.

 

Do i have to use an offset and a limit?

 

Any other idea?

this is my code:

julhelp_2-1643116598737.png

 

thanks in advance

5 REPLIES 5
julhelp
Helper I
Helper I

Hi @BA_Pete,

yes this is the number of rows i am getting in #"total" ...  more precise in #"number" i get 25,429.

 

the problem is that i am getting 5000 rows here:

- #"Custom items"

- #"Expanded data"

 

and here 5:

- #"Expanded Custom"

 

i am not sure if i did it right but i just clicked "count rows" at the right place:

julhelp_0-1643119112441.png

also i am not sure if i use the index correctly. Because i think up to there i have a rounded number of rows.

 

 

Hi @julhelp ,

 

It sounds like you've done the row count correctly as you have identified that your nested object expansions are adding more rows to the original total.

 

It means that your nested objects have more than one row/value in them. Therefore, when you expand the nested object, your #"Number" rows have to duplicate in order to accomodate the extra rows from the nested object.

 

I'm afraid I can't really help you beyond this point. You're going to need to go down your nested object list and click on each cell to see how many records are in each nested object. Any that contain more than 1 record need to be amended as per your desired outcome.

 

You *MAY* be able to use something like this:

Table.RowCount([Custom])

List.Count([data])

 

...in a custom column to count the number of rows in each nested object before expanding, but I haven't tested.

 

Pete

I think the index is not working well because there is the same record any time for the index: 0,1000,2000,3000,4000... 

Hi @julhelp ,

 

I don't know what the relevance of the index is at all.

 

Try this:

 

1) Select your #"number" step. Select the table unique ID column. Go to Home tab > Keep Rows > Keep Duplicates. This should resolve to an empty table (assuming unique records at source).

Delete the keep duplicates step.

 

2) Select your #"Expanded Custom" step. Repeat step 1. This will show you which records are creating duplicate rows in your table, if any.

Delete the keep duplicates step.

 

3) Do the same as step 2 for your #"Custom items" and #"Expanded data" steps. This will show you cumulative duplicates created by each of these steps in aggregate.

 

4) Investigate and resolve as necessary.

 

Pete

BA_Pete
Super User
Super User

Hi @julhelp ,

 

On what basis are you expecting to have 25,429 rows? Is this the number of rows in the source table (#"total")?

If so, then you appear to be introducing duplicate rows into your source table due to crossjoins. You should go through each step in your query taking a row count, paying particular attention to the following steps:

- #"Expanded Custom"

- #"Custom items"

- #"Expanded data"

 

If the row count is different before/after a specific step, then that step is creating the duplicates. I have flagged the above stps to focus on as they all expanded previously nested data, so are the most likely source of crossjoin duplications.

 

Pete

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors