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

Barbers1

New Member
Joined
Mar 29, 2018
Messages
9
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
How exactly did you 'connect' the workbooks?
 

Barbers1

New Member
Joined
Mar 29, 2018
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 29, 2018
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 29, 2018
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
So what happens when you choose the option you indicated?

Are you getting the dates returned properly formatted?
 

Barbers1

New Member
Joined
Mar 29, 2018
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Is there any formatting on the existing worksheet?

What happens if you choose the New worksheet option?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,795
Members
410,708
Latest member
SanTrapGamer
Top