Connecting two workbooks: Dates do not copy across in the right format?

[Barbers1]

Hi,

This morning i have connected two workbooks together using the 'Power Query' add in tab, its worked well and i have all the data, however all my formatting has gone, specifically the dates, instead of showing a short date (00/00/00), it is displaying as a general format 5 digit number and i just cant work out how to change it, so that the format stays and updates automatically. It has also done the same for currency values.

Does anyone know how to keep the format of these columns that have been copied across into my new workbook?

thank you.

 

[Norie]

How exactly did you 'connect' the workbooks?

 

[Barbers1]

Hi Norie,

Thanks for responding.
I opened a new sheet in my workbook, selected the 'Power Query' ribbon tab and selected the excel file i wanted to connected too, found the sheet and loaded it. All the data i there, it just does not have any formatting.
Maybe i am not connecting in the best way, and there is a better, more simpler way to do this?
I did try a formula to pull the data from the original spreadsheet: ='[workbook2.xlxs]Data Input'!$a$25
This formula works well and pulls in everything i need, the only downside is where there is no data on the original spreadsheet it pulls in '0's or puts the date format as 00/01/00, which screws up my data when i am trying to analyse it.

Thank you for your help.

 

[Norie]

I did exactly the same and it pulled the dates from the other file over with formatting.

Once you've selected the other workbook in Power Query what do you see if you click Edit instead of Load?

 

[Barbers1]

If i click edit, i have a new window open (not within my new workbook) with Data Input (2)- Query Editor.
It has basically put the original workbook into columns, were there is no data it says 'null' and it has kept the formatting the same as the original.

 

[Norie]

That's the Power Query editor where, if you wanted, you could manipulate the data.

What happens if you click Close & Load on the toolbar?

 

[Barbers1]

A prompt comes up and says:
" Load to
Select how you want to view this data in your workbook.
1) Table
2) Only Create Connection

Select where the data should be loaded
1) New worksheet
2) Existing worksheet ( I will choose this option)

A tick box that says: Add this data to the data model.

 

[Norie]

So what happens when you choose the option you indicated?

Are you getting the dates returned properly formatted?

 

[Barbers1]

Unfortunately not. I chose to view the data in my workbook as a table and choose the existing worksheet. I do not get any formatting.

 

[Norie]

Is there any formatting on the existing worksheet?

What happens if you choose the New worksheet option?