Format of a column in a data model changes after refresh of data

[helly123]

Hi all,

I have a data model with columns of data that get updated weekly, this data is then reflected in PowerPivots.

When I refresh the data so the pivots update, a column in the data model changes from Whole Number (what I need it to be) to Text. This in turn then breaks some of the measures that use this column.

Does anyone know how I stop this column from changing format? If I go to PowerPivot > Manage > open the data model, i can change it back and this then fixes my issues but i dont want the end user doing this every week.

I dont mind if this can be added to macro to reformat after refresh.

Thanks for any advance,
Helen

 

[helly123]

Hi all,

I have a data model with columns of data that get updated weekly, this data is then reflected in PowerPivots.

When I refresh the data so the pivots update, a column in the data model changes from Whole Number (what I need it to be) to Text. This in turn then breaks some of the measures that use this column.

Does anyone know how I stop this column from changing format? If I go to PowerPivot > Manage > open the data model, i can change it back and this then fixes my issues but i dont want the end user doing this every week.

I dont mind if this can be added to macro to reformat after refresh.

Thanks for any advance,
Helen

Sorry, this should say the column that's changing format is a calculated column.
Helen

 

[jdellasala]

The "base" format of data in the Data Model is dependent on the format of the data pulled in - typically from Power Query, and has types Text, Date, Decimal Number, Whole Number, Currency (2 place Decimal), and TRUE/FALSE. The data coming into the Data Model must be one of those 6 types, and if it has no format - such as type "any" in Power Query, it will go into the Data Model as Text and can't be changed in the DM. If you attempt to change type "any" data in the DM, you'll get this message:

Fix the Query or whatever the source is to provide the data type you need. After that you can change the format in the DM and it should stick.

 

[helly123]

The "base" format of data in the Data Model is dependent on the format of the data pulled in - typically from Power Query, and has types Text, Date, Decimal Number, Whole Number, Currency (2 place Decimal), and TRUE/FALSE. The data coming into the Data Model must be one of those 6 types, and if it has no format - such as type "any" in Power Query, it will go into the Data Model as Text and can't be changed in the DM. If you attempt to change type "any" data in the DM, you'll get this message:
View attachment 81405
Fix the Query or whatever the source is to provide the data type you need. After that you can change the format in the DM and it should stick.

Thank you for the reply.

Can I just clarify this please. The data that is copied into the excel file is from a report ran into a CSV. The last column in the copied data is the 'Minutes Late' Column, I created a 'Sum Min Late' column which is set to Whole Number in the DM and this just adds up the 'Minutes Late' column. I thought that this column would stay as whole number as no data is copied into it but this is the one that is changing when I click to refresh the data.

Is this just expected behaviour?

Thanks,
Helen

 

[jdellasala]

Thank you for the reply.

Can I just clarify this please. The data that is copied into the excel file is from a report ran into a CSV. The last column in the copied data is the 'Minutes Late' Column, I created a 'Sum Min Late' column which is set to Whole Number in the DM and this just adds up the 'Minutes Late' column. I thought that this column would stay as whole number as no data is copied into it but this is the one that is changing when I click to refresh the data.

Is this just expected behaviour?

Thanks,
Helen


View attachment 81414

Here's your problem - "copied into the excel file is from a report ran into a CSV". Why are you copying instead of pulling it into Power Query where you can make any adjustments you might need, assign the proper data types, and load it directly into the Data Model with or without an interim table? Also, this would mean just hitting Refresh All to update the Data Model instead of Opening the CSV, Copying the CSV, opening the Workbook with the Data Model, Pasting Values from the CSV....
If you insist on using PASTE, you must pre-format the columns, and then ONLY PASTE VALUES.
What are the data types for the columns Minutes Late is SUMming? They'd have to be numeric for the calculation to work.

 

[helly123]

Here's your problem - "copied into the excel file is from a report ran into a CSV". Why are you copying instead of pulling it into Power Query where you can make any adjustments you might need, assign the proper data types, and load it directly into the Data Model with or without an interim table? Also, this would mean just hitting Refresh All to update the Data Model instead of Opening the CSV, Copying the CSV, opening the Workbook with the Data Model, Pasting Values from the CSV....
If you insist on using PASTE, you must pre-format the columns, and then ONLY PASTE VALUES.
What are the data types for the columns Minutes Late is SUMming? They'd have to be numeric for the calculation to work.

Thank you for this. This isn't an area i know a lot about so I am just trying to sort something out for a client.

The Minutes Late data come from a report and is set to number on the excel table. (i tried setting it to number on the csv and pasting values but it still changes to text upon refresh)

I only added the Sum Min Late column as i ithought this wouldnt be affected by the Refresh routnine.

Thank you for your time, i will have a look into how else i can set this up.

 

[jdellasala]

Thank you for this. This isn't an area i know a lot about so I am just trying to sort something out for a client.

The Minutes Late data come from a report and is set to number on the excel table. (i tried setting it to number on the csv and pasting values but it still changes to text upon refresh)

I only added the Sum Min Late column as i ithought this wouldnt be affected by the Refresh routnine.

Thank you for your time, i will have a look into how else i can set this up.

Power Query is very easy to use in its basic functions. There are great YouTube playlists here and here. I prefer the later as each video is relatively short and has before and after worksheets for each video. It's well worth learning as it can turn literally HOURS of work into a single click!