Power Query and cell formatting

[RodneyW]

Good morning. I'm using Power Query to import some data. When I update data in the original file, then refresh in the final file, some of the number formatting doesn't stick. For example, some cells should be formatted as % but they appear as numbers with decimals. IE 68% shows up as .68 and I have to manually change it to %. IS there anyway to avoid this step?

Thanks in advance

 

[JEC]

If you format the table column in Excel as %, it should work

 

[RodneyW]

If you format the table column in Excel as %, it should work

I've checked both the source and destination cells and they are all formatted as percentage. If I update the source file, then refresh on the destination file, the data does indeed change and reflect the updates I made, but the formatting changes in the destination file.

 

[JEC]

are you sure your column in PQ is formatted as decimal number?

 

[RodneyW]

are you sure your column in PQ is formatted as decimal number?

I've just confirmed, all cells I'm having an issue with are formatted in both files as "percentage' with no decimal places.

 

[RodneyW]

I've just confirmed, all cells I'm having an issue with are formatted in both files as "percentage' with no decimal places.

OK, I think I understand better. I opened the PQ and am looking at those columns and they are indeed formatted as decimal. Trying to figure out how to convert those to %

 

[JEC]

I think you misunderstood my question. Post some sample data instead, where it would go wrong

 

[JEC]

If every single record in these columns are formatted as decimal, you really should be able to do the formatting in Excel, after loading the table. Try to format the table column only. So not the whole sheetcolumn.

Also, click on your Query table(in the Excel sheet), go to table design —> properties—> make sure you have “preserve cell formatting” checked

 

[RodneyW]

If every single record in these columns are formatted as decimal, you really should be able to do the formatting in Excel, after loading the table. Try to format the table column only. So not the whole sheetcolumn.

Also, click on your Query table(in the Excel sheet), go to table design —> properties—> make sure you have “preserve cell formatting” checked

That helped. THANK YOU

 

[JEC]

Great! Cheers