Certain data tables not updating on visualizations

[NamNguyenJH]

Hey all,

Brand new user to Power BI here. I have played around with the software for almost a month now and have done most of the learning myself, however i have seemed to have found myself in a situation that looks trivial but i am tearing my hair out to solve.

Context: I am making a report that tracks my team's activity throughout the week. When they visit certain areas in the business it is recorded onto a table in an excel sheet stored on Sharepoint. In the same excel sheet, i have a table that summarises their activity throughout the week and scores them a % based on a target.

Problem: The table that is displaying their overall % target never updates onto the report and my gauge visualisations are left showing incorrect data. 

Extra Data: The report is linked to 25 other different tables from the same excel sheet which seems to have no problems.

This is a small snapshot of one corner of one of the pages of the report. As you can see the Guage Visual is at 0%. However, the table underneath is also linked to the same excel and sheet and updates just fine, it is showing 9 out of 21. I am expecting the guage to display 42.8%

Now when i open the actual excel file on sharepoint and look at the tables i see this (again small screenshot of the table)

So to me excel is calculating the data correctly!

Finally to try to trace the issue, i check my data on Power BI desktop.

Not surprisingly this one is incorrect.

Is anyone able to tell me where i am going wrong? All the data to sharepoint is connected via Web Source and as far as i can tell, the tables are updating just fine. I have the file on scheduled refresh 8 times a day but this one particular table seems to have a mind of its own. I feel like this should be a relatively simple problem to fix as i have played with many Web Source connections with other reports perfectly fine.

Any help would be sincerely appreciated

Regards,

Nam

[v-shex-msft]

Hi @NamNguyenJH,

It seems like you are using excel formulas to processing these calculations and try to get data to power bi but these calculations fields keeps blank on power bi side, right?

For your scenario, DAX formulas are calculated base on its contents. (these calculations are dynamic change based on current row contents) Power bi recognizes them as table fields and loading these fields without processing with their expressions, so you get the blank field values and formatted as the default result 0.

I'd like to suggest you use the import feature to loading excel contents, it also lading these calculate formulas to power bi.

Import Excel workbooks into Power BI Desktop 

Regards,

Xiaoxin Sheng

[NamNguyenJH]

@v-shex-msft 

Hi Xiaoxin,

Thank you for your reply. Yes i am using excel formulas, however i am doing it for all 26 of my tables. Please see below for a screen snippet of my excel sheet with formulas shown.

They simply all just refer to another part of the excel document for their values. However it is just the table at the top that seems to have a mismatch with information and doesnt update properly, which is reflecting in my power BI report.

I refreshed the report and took a snapshot of both the excel table vs the data in power BI, as you can see they are vastly different and i dont think it is an issue with displaying blank fields. It feels like Power BI is reading old data and refusing to update with newer information. 

What makes it worse is that it seems to be unique to just the one table. I have deleted the table and connections, re-created the table under a different name and still result in the same problem.

Thank you again in advance for your help.

Regards,

Nam

[v-shex-msft]

HI @NamNguyenJH,

Have you checked the backend query table connection to confirm if they mapping to database or link to excel file? If they are linked to an excel file, power bi refresh operations not triggered the excel file data refresh.

You need manually update and save changes on excel side then you can use the refresh button to bring the latest data to power bi desktop.

Regards,

Xiaoxin Sheng

[NamNguyenJH · Accepted solution]

@v-shex-msft 

Hi Xiaoxin,

Thank you very much for your advice. I checked out the query table and found the problem!

The data was being stored as a whole number rather than a decimal number.

The reason why this was probably happening was due to when i originally obtained and transformed the data, my excel table had the numbers in a table like this

0    0.55    1    0.97    0.66    1    1    0     0.2   0.45    

When it started transforming the data, it probably registered the numbers as

W    D     W     D     D     W    W    W    D    D

Where        W = Whole Number      and         D = Decimal Number

So for live comparison

VS

After i made the change the numbers have been spot on.

Lesson learned: Always pay attention to how the data is formatted and what it is being transformed into

Thanks for your help again.

Regards,

Nam