Excel data source not updating data type correctly

[preetks]

Hi,

I have a pbix file which is connected to an Excel file on SharePoint as the data source. One of the columns in the Excel file is filled with numbers. I added new rows, where some of the entries are in decimals. When I refresh the Excel dataset, all the decimal-point entries are getting removed within that column.

When I view the "Column Type" in PowerBI desktop, it shows as "Whole number" for the specific column. That would explain why the values which are in decimals. I am unable to change the data type in the toolbar. When I go to Data Source Options, it says - "options are not available because the file uses a live connection to another model".

Is there any way to change the datatype for an existing column in the Excel based dataset? Or do I need to recreate the whole dataset and recreate everything in the PowerBI report?

Thanks.

[Anonymous]

Hi @preetks ,

You could check "Never detect column types and headers for unstructured sources" so that the column type won't be changed when loading data.

Best Regards,

Jay

[preetks]

@amitchandak - As mentioned in the original post, the data source is an Excel file on SharePoint. And the options to transform the data type are disabled.

This is what I see when I view the properties of the column in the dataset. Data type is set to whole number and I am unable to change it.

When I go to data options, it doesn't let me change those also.

Hope this clarifies.

[amitchandak]

@preetks , Open Power Query/Transform Data. There you have the option to change the data type there.  You can change it there. Or you can right click on the table in power query, open advance editor. Find the column type should be int.64 , change it to numeric

My Video has steps other than the change of data type.

https://youtu.be/h0yLtceT0i4

Check the data type when I add the new column. You will get the correct one number (numeric or decimal)

Post that; go and change again in Column tools if needed