Dataset was changed but cannot refresh

[ffirda99]

Hi,

I have this data, from Excel 

I already changed that 'red column' in my Excel, but I don't know why it was not replaced with the new one. How to fix this? Because the data can't convert to time type. Thank you.

[RossEdwards]

The existing data is of type "Text", which you can see in your screenshot as it says "Text Filters".  Your new shot shows "Date Filters" which means it has successfully data typed it as one of Date/Time/DateTime.

This says to me that your Power Query code and the mix of data types in the "existing data" do not work.  You would need to add additional steps in your Power Query code to transform the "existing data" further to allow it to data type correctly.

[RossEdwards]

Inside your Power Query, you may need to add some steps to review and update the data type.  Something to remember with Excel is that any cell has "Display" values and "Actual" values.  Its possible for you to change how a value is displayed in Excel but not actually change the value that is stored.

Power Query, when bringing in Excel data will often contain a mix of Display and Actual values, depending on what the engine finds in the source.  This can often lead to inconsistant typing and potentially errors.

The best thing you can do add steps into your Power Query to enforce data typing. You may need some additional steps to look out for data that is outside of what the automation can handle on its own.  It might be possible that the records like "12:15" are being seen as TEXT and not TIME.

You can use the 'Keep Rows' -> Errors, to see if certain rows are being excluded due to data typing issues.

[ffirda99]

@RossEdwards ,

even though I changed my excel now to Text Type, its not impact to the existing data?

[RossEdwards · Accepted solution]

The data typing you set in Excel will only have a minor impact on your Power BI. This will generally effect the "display" data in excel, rather than how its actually stored in the file itself.

Its the data typing steps you set in your Power Query that you need to concentrate on.  You need to look in "Transform Data" inside Power BI and make sure you have your field set to the correct data type.

I would recommend have the column set to "Time" and not TEXT.  Once you have it set to the correct "Time" data type, you can use the column tools to set how you want it displayed.

[ffirda99]

@RossEdwards ,

I tried to re-upload,

this is my existing excel in Power BI

and, this is the new one.

the new data can refresh what I edited before. But the existing data, cannot do it. Why? and how to fix it? Thank you!