Subject: Data Discrepancy: Full Data in Power Query, Missing Data in Power BI - Seeking Solutions

[Asma-k]

I'm pulling data from an API using Power Query. I have two main tables: "Shops" and "Sales." The "Shops" table contains a list of shop IDs, along with a "FromDate" and "ToDate" column. The "Sales" table is generated by invoking a custom function (GetSalesData) on the "Shops" table, which calls the API for each shop ID and date range.

The Problem:

Inside Power Query, I can see all the data as expected. I've verified that the GetSalesData function is retrieving data for every shop ID. When I preview the "Sales" table in Power Query, all shop IDs and their corresponding sales data are present.

However, when I "Close & Apply" and view the data in the Power BI report view, some shop IDs are missing from the "Sales" table. It's not a consistent pattern – some IDs are always missing, while others appear intermittently. I've tried:

• Explicit data type conversions: In both the GetSalesData function and the "Sales" table query, I'm explicitly setting data types to ensure consistency.
• Null value handling: I'm replacing null values with default values to prevent potential errors.
• Simplified test queries: I've created simplified queries to isolate the issue, and I've found that the GetSalesData function itself is working correctly.
• API data validation: I've used Postman and curl to verify the raw API data, and it appears to be consistent.
• New Power BI files: I've created new Power BI files to rule out data model corruption.
• Token Refreshing: I have made sure my API token is always up to date.
• Simplified expand table process: I have tried expanding only one column at a time.
• Inspecting individual "SalesData" tables: I have made sure that the column names are consistent in all tables, before the expand table step.

Despite these efforts, the issue persists. The data is complete in Power Query, but incomplete in Power BI. Can anyone guide?

[V-yubandi-msft]

Hi @Asma-k ,

Please consider marking it as the Accepted Solution. This helps others in the community who may be facing the same issue find the solution more easily.

Thank You.

[V-yubandi-msft]

Hi @Asma-k ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

[Asma-k · Accepted solution]

i couldn't figure out what was the issue. i just created a new file and started from scatch and now i can see whole data. 

[V-yubandi-msft]

If your issue is resolved, please consider marking it as the Accepted Solution. This helps others in the community who may be facing the same issue find the solution more easily.

[V-yubandi-msft]

Hello @Asma-k ,

Thank you all for sharing the details and for trying different troubleshooting steps. Here are a few additional things you can check to sort it out.

1. Make sure there aren’t any filters in the report or Data View that might be causing the issue.

2. Power BI treats "Shop123" and "shop123" as different. Ensure all shop IDs are in a consistent format to avoid mismatches.

3. Try exporting the data from Power Query and comparing it with what's in Power BI to see if any records are missing.

If the issue still persists, you can log API requests to confirm if all records are being fetched correctly.

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

[ZhangKun]

Maybe it's a cache issue. You can try setting the IsRetry parameter of the Web.Contents function to true to ignore the cache.

[Asma-k]

I checked, it is not because of cache. 

[ZhangKun]

Perhaps you can only use Fiddler to capture the request to see what happened. For example, the API does not allow you to access it frequently and returns an error.

[ToddChitt]

How many Shops are you talking about? 50? 100? 1,000? More? And when you get the data into Power BI, is the number of complete and number of missing Shops always the same? Is it possible the API is limiting the number of times that you can call it to get Sales?

Once you retreive the data for Sales, does it ever change? In other words, would you need to refresh it again tomorrow? If the Sales data is static, consider a repository of the data between the API call and Power BI, like maybe a Fabric Lakehouse or Warehouse, with API calls made via a Data Pipeline. That way you call the API once per Shop, get the data, then don't need to make additinal API calls for that Shop.

[Asma-k]

We are working with 19 shops in total. The shops that are showing as missing values are always the same, even though the data is present in Power Query.

I have checked the call limitations but i don't think this is the problem, because the shops that are missing does not contain alot of data. 
Regarding the sales data, it is not entirely static. While historical data remains consistent, we need to refresh the data daily to capture the sales from the previous day.

[ToddChitt]

HOW are you verifying that you are missing Sales data for certain shops? Is it simply by looking at the "Table view" page in Power BI Desktop and filtering the Sales table by Shop ID? Do this: highlight the Shop ID column in the Data tab. At the bottom left of the screen you should see some statistics of the column: number of rows and number of distinct values. From what I understand, if you have 19 distinct Shops in that table, ideally, you should see a stastic of "19 distince values" when you highlight that column. 

Or are you relying on a Visualization on a Report page? Can you show a screenshot of your data?

Also, Power BI is Case Sensitive data. So Shop Id of "A1A" would not 'match' Shop Id of "a1a" in the Sales table.