Help using power query and a mapping table to append multiple worksheets and files to one file

Stacker

Board Regular
Joined
Jul 11, 2021
Messages
87
Office Version
  1. 365
Platform
  1. Windows
I am following this guide and I successfully did the first 5 or so minutes and created a mapping table. I have three files, a blank template, and two files with dummy data.

Template

https://imgur.com/RfQ9joM

File 1


File 2


I want to append the contents of file 1 and 2, to the blank template. When i try to transform the sample file I used this formula.

= Table.TransformColumnNames(Table3_Table, each

List.Accumulate(Table.ToRecords(Table23),_,

(state, Current)=> Text.Replace(Text.Upper(state),current

[BEFORE], current [AFTER]) ))

Which presents this error:

Expression.Error: The name 'Table3_Table' wasn't recognized. Make sure it's spelled correctly.

Here is the source part of the transformation




Top is the header table but without any content

Table3 is the template but without any info so just headings of the final doc



Table 23 is the mapping table mentioned earlier in the vid.

What do i do?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I am not sure if I can help you but
  • do you have a headings mapping table set up an imported as a connection only ?
  • I assume we are only loading 2 files at the moment do they both have the same table name for data and what is that name ?
 
Upvote 0
I do. That was the first thing I did. My video went through every individual file that I wanted to use so one can familairise themselves with the data, and from around 1 minute you can see me creating the mapping table as a connection and it Table23 as shown in my video. They have different table names, which I think the start of my video shows.
I am not sure if I can help you but
  • do you have a headings mapping table set up an imported as a connection only ?
  • I assume we are only loading 2 files at the moment do they both have the same table name for data and what is that name ?
 
Upvote 0
They have different table names, which I think the start of my video shows.

Expression.Error: The name 'Table3_Table' wasn't recognized. Make sure it's spelled correctly.

I suspect that may have lot to do with your issue.
Leila's video @4:20 "The table that all of these files have in common is called TSalary"

Having a common table name would be typical for a merge spreadsheet scenario.
 
Upvote 0
I suspect that may have lot to do with your issue.
Leila's video @4:20 "The table that all of these files have in common is called TSalary"

Having a common table name would be typical for a merge spreadsheet scenario.
So should i change the table name so that they all have the same table name? The transformation query doesn't even recognise the name.
 
Upvote 0
Yes both of your source spreadsheets should have the same table name.
You will need to change your "source" on the sample file query and possibly also the merge query.
 
Upvote 0
I changed the name of all of them to Table1 however when i try to append them it doesnt append and puts all the files with the bulk data apart instead of on top of each other and ignores the template where its an error
 
Upvote 0
Yes both of your source spreadsheets should have the same table name.
You will need to change your "source" on the sample file query and possibly also the merge query.
So here are all the tables waiting to be transformed.


When I click the tables here the two tables and the template seem to be working just fine.

I tried to combine the files and I am presented with this




When I click combine it shows this




The second file is not appended underneath the top file as it should.

and when i scroll right

I go back a step and click the left and right arrow. I then used the original column names as prefix and it shows this.


The dummy data for the first file seems proper. However, the second file's data is not appended underneath it as it should. Here is how the first couple columns look like


Instead I have to scroll right until the second files data shows up. It looks like this

imgur.com



I edited the column names and deleted the column names so I can have everything fit. So what should I do to make sure that

  1. The matching columns in the respective files are matched onto where they are onto the template
  2. The data in the two files is appended onto the template file with the second files data directly following the first.
 
Upvote 0
I would suggest you start fresh, it has a lot of previous tries in there.
  • You now have the Table Name in Reddit1 & Reddit2
    (I think Table1 is a meaningless name and easily confused and I would use a more meaningful name)
  • Import your mapping table as connection only
    What is the name of that table - ideally also something meaningful)
  • Get Data > From folder > Transform
  • I don't know what template is doing in the import file steps.
    This sounds more like the destination of the output.
    Is it the same file as the mapping table.
    I would filter so you only only get the files starting with Reddit
  • In transform sample file > click on the navigation step and hit fx (add custom function)
    • It should populate with table name: in your case probably Table1
      Add_Table to that and in the M code below replace my tblSupplier_Table with your Table1_Table
    • Then if BEFORE and AFTER aren't the headings for column 1 & 2 of your mapping table change those words for your column 1 and 2 headings (it is case sensitive)
    • My Mapping column 1 is the same case as my import file headings so I have not used the additional Text.Upper function around (state)
    • Copy the below function with the change to the table name change above into your fx formula box

      Power Query:
      = Table.TransformColumnNames (tblSupplier_Table, each List.Accumulate(Table.ToRecords(tblMapping),_,(state,current) => Text.Replace(state,current[BEFORE],current[AFTER])))
  • A new Imported Excel function will be created > delete that step
  • In the Main merge query remove the last Changed Type step (It is the step that appears in your pictures)
    It has old column names in it and you need it gone.
  • Select all in the Main query Ctrl+A and Transform > Detect Data Type
See how you go with the above.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top