Sharepoint online list to PBI - Not showing up

[bbbbbiiiii]

Hi I manage to connect a sharepoint online list to my PBI dashboard. The list has 559 rows but when I connect it, it only show 500. I checked thru the sharepoint list and it seems OK. When I do a eye ball check, some of the older dated rows are missing. Any idea what went wrong?

[venal]

@bbbbbiiiii 

After loading the data in Power BI Desktop, please try to drag a table visual and select the table columns and export it.

After export, you can check for the data count from sharepoint and power bi desktop.

If you have any concerns, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[parry2k]

@venal just curious why it required export to compare, it should be showing up in power bi, if @bbbbbiiiii already identified it is missing row I believe there is something else going ona

[venal]

@parry2k 

Yes, it sould display here is Power BI like below.

For Example:-I have two tables in SQL Server.

select count(1) from [dbo].[salesorderdetails] --> 75741

select count(1) from [dbo].[salesorderdetails1] --> 45576

Tried to load the above data into Power BI Desktop and it is showing the table count in Data view in the bottom like in the below snippet.

Some times, the request is not completed successfully and it will display less data( original 599 -> 500 is showing).

So to validate that, i suggested to compare the data from sharepoint and Desktop.

@bbbbbiiiii Can you please clear the cache and try to re-load the data once again.

File >> Options & Settings >> Options >> Cache (Data Cache, Q&A Cache).

Thanks,

venal.

[parry2k]

@venal never experienced this, if it is import , I expect the whole data is loaded until there is a filter in power query 

[venal]

@parry2k 

Yes, you are right.

Now we need to look into the Power Query.

I hope that we can find it from Edit Queries >> Applied stpes also.

[bbbbbiiiii]

Hi all,

Thanks for all the replies. I try all the steps as well

This is the advanced editor in my PBI. 

I am not very good at the Power Query, can someone guide me how to get the script?

let
Source = Web.Page(Web.Contents("https://xxxxcorp.sharepoint.com/xxxx")),
Data0 = Source{0}[Data],
#"Removed Top Rows" = Table.Skip(Data0,1),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{""}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Date & Time of Engagement", "Date & Time of Engagement - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Date & Time of Engagement - Copy", "DATE_TABLE"}}),
#"Inserted First Characters" = Table.AddColumn(#"Renamed Columns", "First Characters", each Text.Start([DATE_TABLE], 10), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted First Characters",{{"First Characters", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"First Characters", "DATE"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "DATE", "DATE - Copy")
in
#"Duplicated Column1"

As of today, it should have 580 rows

[bbbbbiiiii · Accepted solution]

I found the solution. 😑

My IT side has limit the sharepoint list to 500 entries. 🤣Thanks everyone for the help!  Learnt something!

[parry2k]

@bbbbbiiiii are you doing any filters in power query when loadin the data. Share Power Query script