Copying data from one excel file to another

[shankar_iyer83]

Hi,

I need to copy data from other file for the days of month. However, when I use hyperlink and drag it down/left/right .it does not pick correct values.

How to do this linking for all days? Attaching file with sample data and kind of output I want

I need to copy data from "Original file.xlsx" (the format is same as I mentioned in file )

and paste it in "Avg Bank balance sample.xlsx" file (I have to manually copy and paste it as of now)



Please help

 

[shankar_iyer83]

Hi,

I need to copy data from other file for the days of month. However, when I use hyperlink and drag it down/left/right .it does not pick correct values.

How to do this linking for all days? Attaching file with sample data and kind of output I want

I need to copy data from "Original file.xlsx" (the format is same as I mentioned in file )

and paste it in "Avg Bank balance sample.xlsx" file (I have to manually copy and paste it as of now)



Please help

 

[S. Das]

I have a doubt...
You will enter the amount for bank 1 to bank5 in those 5 position and you want the data should copy in appropriate position.
Am I correct??
And what about the date....you will enter it or it will come automatically??

 

[GraH - Guido]

Dear shankar_iyer83

Are you using Excel 2010/13/16 for windows, then this kind of transformation is easily performed with a Power Query (PQ).

Basically this pattern is a transpose.
In code it looks like this, but the beauty of PQ is, that you can code all this by simply using the UI.

let
  Source = Excel.Workbook(File.Contents("G:\Uploads\ShankarFile.xlsx"), null, true),
  #"data set_Sheet" = Source{[Item="data set",Kind="Sheet"]}[Data],
  #"Removed Top Rows" = Table.Skip(#"data set_Sheet",2),
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
  #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "Currency"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> null and [Column1] <> "EXPORT ")),
  #"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
  #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
  #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
  #"Added Custom" = Table.AddColumn(#"Promoted Headers1", "Date", each if Text.StartsWith([Column1],"Col") then null else [Column1]),
  #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Date] <> null)),
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Date"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type date}}),
  #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column1", null}}),
  #"Renamed Columns1" = Table.RenameColumns(#"Replaced Errors",{{"Column1", "Date"}})
in
  #"Renamed Columns1"

I have added a sample file (made with version 2016).
The first step of PQ always refers to the source, in this case your original Excel file. To change the path of reference, click the gear icon next to the first PQ applied steps. It kinda looks like this:


If you need some further assistance with this, in case you like this kind of solution that is, please let me know. Happy to document in more detail if required.

PS: Like macros, PQs can run over and over again. Simply press refresh, with a right click, on the result table. The pattern will be reapplied to the new data.

Cheers
G.

 

[shankar_iyer83]

Hi G,

Yes, I am using excel 2016. Sorry I did not understood how to do it and make changes. I could find "New query" tab under "data" but after that I could not understand

Could you please explain it in detail. I do not know much coding/VBA but will try to apply here with whatever I know

Dear shankar_iyer83

Are you using Excel 2010/13/16 for windows, then this kind of transformation is easily performed with a Power Query (PQ).

Basically this pattern is a transpose.
In code it looks like this, but the beauty of PQ is, that you can code all this by simply using the UI.

let
  Source = Excel.Workbook(File.Contents("G:\Uploads\ShankarFile.xlsx"), null, true),
  #"data set_Sheet" = Source{[Item="data set",Kind="Sheet"]}[Data],
  #"Removed Top Rows" = Table.Skip(#"data set_Sheet",2),
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
  #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "Currency"}}),
  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> null and [Column1] <> "EXPORT ")),
  #"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
  #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
  #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
  #"Added Custom" = Table.AddColumn(#"Promoted Headers1", "Date", each if Text.StartsWith([Column1],"Col") then null else [Column1]),
  #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Date] <> null)),
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Date"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type date}}),
  #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column1", null}}),
  #"Renamed Columns1" = Table.RenameColumns(#"Replaced Errors",{{"Column1", "Date"}})
in
  #"Renamed Columns1"

I have added a sample file (made with version 2016).
The first step of PQ always refers to the source, in this case your original Excel file. To change the path of reference, click the gear icon next to the first PQ applied steps. It kinda looks like this:
View attachment 48654

If you need some further assistance with this, in case you like this kind of solution that is, please let me know. Happy to document in more detail if required.

PS: Like macros, PQs can run over and over again. Simply press refresh, with a right click, on the result table. The pattern will be reapplied to the new data.

Cheers
G.

 

[shankar_iyer83]

I can enter date manually in first column and drag( thats not a problem) and yes data in those five columns (actually there are more columns) should copy from original file corrosponding to that date

Hope I am clear

I have a doubt...
You will enter the amount for bank 1 to bank5 in those 5 position and you want the data should copy in appropriate position.
Am I correct??
And what about the date....you will enter it or it will come automatically??

 

[GraH - Guido]

Hi G,

Yes, I am using excel 2016. Sorry I did not understood how to do it and make changes. I could find "New query" tab under "data" but after that I could not understand

Could you please explain it in detail. I do not know much coding/VBA but will try to apply here with whatever I know

Like I've said PQ is actually not about coding to start with.
I'll try to guide you a bit.
On the data tab on the ribbon you should see View Query Pane. Click it to view the query I added.
In that pane, click on the query query and select edit to open the PQ window.
Then you should see something like this:
- at the left the list of queries, you should see the same one as in the pane
- in the middle you see the preview window (the intermediate result of the transformation steps)
- at the right the list applied steps: if you click on them, the preview window is updated. The gear icon allows to change some parameters of that specific step. Each of these steps were performed with actions on the ribbon.

Thanks to Ninja Narayan, I can give you 2 links to get you started with PQ.
https://chandoo.org/wp/2015/07/30/intro-to-power-query-podcast/
https://chandoo.org/wp/tag/power-query/

or google excelIsFun + Power Query on YouTube.

I hope that helps.
G.

 

[shankar_iyer83]

Hi G,

Thanks for reply. I started to do as you told. I opened your file "ShankarPQTranspose".xlsx but I get this error?



Also, sorry if it sounds silly, can you guide me more on what exactly I have to do with this file and how to make changes using this file to my earlier posted files "Original file" and Avg balance" file to get desired answer

Like I've said PQ is actually not about coding to start with.
I'll try to guide you a bit.
On the data tab on the ribbon you should see View Query Pane. Click it to view the query I added.
In that pane, click on the query query and select edit to open the PQ window.
Then you should see something like this:
- at the left the list of queries, you should see the same one as in the pane
- in the middle you see the preview window (the intermediate result of the transformation steps)
- at the right the list applied steps: if you click on them, the preview window is updated. The gear icon allows to change some parameters of that specific step. Each of these steps were performed with actions on the ribbon.
View attachment 48980
Thanks to Ninja Narayan, I can give you 2 links to get you started with PQ.
https://chandoo.org/wp/2015/07/30/intro-to-power-query-podcast/
https://chandoo.org/wp/tag/power-query/

or google excelIsFun + Power Query on YouTube.

I hope that helps.
G.

 

[GraH - Guido]

Hi G,

Thanks for reply. I started to do as you told. I opened your file "ShankarPQTranspose".xlsx but I get this error?

View attachment 49079

Also, sorry if it sounds silly, can you guide me more on what exactly I have to do with this file and how to make changes using this file to my earlier posted files "Original file" and Avg balance" file to get desired answer

Click on the gear icon of the first step, which is always source. In there is the path to your file. Change this and magic happens ;-)

The source is the link to your file "original". The file containing the Power Query (get & transform) will be the AVG bank Balance file.

Small steps will get you there. If you're stuck again, just ask.

 

[shankar_iyer83]

Thanks G! I seem to get slowly

Now need more of your inputs here! My actual working official file where I need to use has data for whole month working days (say for decemeber 1 to December 31st barring saturday/sundays). If I use your file..it doesnt shows full month data. I may have to edit code something? How to do it?

 

[GraH - Guido]

Thanks G! I seem to get slowly

Now need more of your inputs here! My actual working official file where I need to use has data for whole month working days (say for decemeber 1 to December 31st barring saturday/sundays). If I use your file..it doesnt shows full month data. I may have to edit code something? How to do it?

Do you mean in the preview or after a full refresh?

EDIT: I think I found where the issue is coming from. In the source step, click the Table object. Like below. In the sample file you only had 14 columns. Now I guess you have a lot more of them. Click on those 2 arrows pointing up. And select all the columns you need. You'll get a warning that you are going to insert a new step. Accept that one. Then delete the first one that follows (that is the old step that only contains 14 columns). Then we will see how far we come.


Or start from a new query all together (from file) and try to follow the steps of my example.

 

[GraH - Guido]

Updated the one I made to include columns up to BZ (see preview of step2 it contains 80 columns now). I'm expecting it to work.

 

[shankar_iyer83]

Thanks G!
How did you start to create code etc so that I can try it on own when I have time I am not a VBA/Macro guy so if it involves that I will not poke my nose further!

Updated the one I made to include columns up to BZ (see preview of step2 it contains 80 columns now). I'm expecting it to work.

Tha

 

[GraH - Guido]

Thanks G!
How did you start to create code etc so that I can try it on own when I have time I am not a VBA/Macro guy so if it involves that I will not poke my nose further!



Tha

Like I've said, the code is created by using the user interface. Sometimes you need to write a formula via a custom column. But in this case I never needed to that.
The advanced to very advanced users, they really write code to do the impossible. That requires in-depth knowledge of the M language.
Honestly, the things you can already achieve by simply using the UI are simply amazing. I do invite you to review some of the sources I mentioned before and to experiment. PQ basics can be learned in hours. The mouse as your best friend, your magic wand if you like.
I'll take some time to make an almost step by step tutorial for your case.

 

[shankar_iyer83]

Thanks! I shall wait for that

I first tried to use Vlookup then Match+ Index etc.but somehow was not able to arrive at consistent result

Like I've said, the code is created by using the user interface. Sometimes you need to write a formula via a custom column. But in this case I never needed to that.
The advanced to very advanced users, they really write code to do the impossible. That requires in-depth knowledge of the M language.
Honestly, the things you can already achieve by simply using the UI are simply amazing. I do invite you to review some of the sources I mentioned before and to experiment. PQ basics can be learned in hours. The mouse as your best friend, your magic wand if you like.
I'll take some time to make an almost step by step tutorial for your case.

 

[GraH - Guido]

as promised... File with a step by step guide.

 

[shankar_iyer83]

Thanks G!

I managed to have a look(will work on it later over the weekend)

Have send you message in your inbox!

as promised... File with a step by step guide.