Running Total Power Query working fine but missing data when loaded to PowerBI Model

[threw001]

Hi guys,

I have created a query that calculates a running total based on categories. This query is running fine on Power Query however once I load the data into PowerBI data model I get blanks in my Running Total column that were not blanks in Power Query.

I have re-loaded the query multiple times and I am getting the same result.

Notice in the below example every index has a running total in Power Query, however in PowerBI I am getting a lot of blanks

Same exact table:

Power Query

Table (in PowerBI)

Thanks

[SamInogic · Accepted solution]

Hi,

Based on our understanding, you are experiencing an issue where the running total column works fine in Power Query but shows missing data when loaded into the Power BI model.

This discrepancy between Power Query and Power BI is often due to how Power BI handles data once it's loaded into the data model. Here are some possible reasons and solutions:

1. Check for Filters in Power BI

Power BI visuals (tables or charts) might have filters applied by default, causing certain rows to not display properly.

• Check for filters: Open the Filters pane in Power BI to see if there are any filters applied that might exclude rows.
• Remove any unnecessary filters to see if that resolves the issue.

2. Missing Data or Incomplete Loading

There could be an issue with how the data is being loaded into the model from Power Query. It’s possible that the data was only partially loaded, which might explain why certain running totals are missing.

• Reload the data: Click on Home > Refresh in Power BI to reload the dataset and ensure all rows are properly loaded.
• Ensure proper data refresh settings: Sometimes Power BI may not refresh all queries, so ensure everything is fully up to date.

3. Data Type Mismatch

In Power Query, the data might appear correct, but Power BI could interpret the "Index" or "Running Total" columns with the wrong data type.

• In Power BI, go to Model View and verify that both the Index and Running Total columns are set to the correct data types (e.g., whole number for "Index" and decimal number for "Running Total").
• If they are incorrect, change the data types and reload the data.

4. Auto-Aggregation

In Power BI, columns may be automatically aggregated, causing some values to appear as blanks in tables or other visuals.

• Disable aggregation:
• Click on the "Running Total" column in the Fields pane.
• Under Properties, make sure Summarization is set to Do Not Summarize.

5. Check Power Query Steps

• Revisit your Applied Steps in Power Query to ensure that no transformations are being inadvertently dropped or altered when loading into Power BI.
• Specifically, ensure that no step is adding nulls or blanks to your running total column.

6. Re-import the Data

If none of the above methods work, try reloading the query completely:

• Go to Power Query Editor, and ensure everything is correct.
• Re-load the dataset into Power BI by using Close & Apply.

Thanks!

[elitesmitpatel]

Data Type Mismatch:
Sometimes, data types in Power Query may differ from those in the Power BI data model. Ensure that the data types for the columns involved in the running total calculation are consistent in both Power Query and Power BI.

Blank or Null Values:
In Power BI, null or blank values in any row used for your running total calculation might cause blanks in the calculated column.

Solution:

Check if there are any null or blank values in the columns you're using for the running total calculation.
In Power Query, you can replace null values with zeros or other default values using the "Replace Values" functionality.

The filter context in Power BI might differ from the unfiltered table view in Power Query, causing the calculation to return blanks.

Solution:

If there are any slicers or filters applied in Power BI, make sure they aren’t unintentionally filtering out data that’s necessary for your running total calculation.
Use ALL() or REMOVEFILTERS() DAX functions to remove unwanted filter contexts in your measure.

Power Query Applied Steps:
If there are any applied steps in Power Query that might not be loading properly into Power BI, it could also cause discrepancies between the two.

Solution:

Review all applied steps in Power Query and ensure they’re being fully applied when loading into Power BI.
Refresh the data after making sure all applied steps are correct.

Column Order:
If your running total calculation depends on the order of rows (based on an index or date), ensure the sort order is consistent when the data is loaded into Power BI.

Solution:

In Power BI, set the correct sort order for your table by sorting by the appropriate column (like date or index).