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

[Stacker]

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

https://imgur.com/MykXsri

File 2

https://imgur.com/zw0yXYJ

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

https://imgur.com/Wur8PLk

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?

 

[Stacker]

Here is a video I made of waht I am trying to do

 

[Alex Blakenburg]

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 ?

 

[Stacker]

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 ?

 

[Alex Blakenburg]

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.

 

[Stacker]

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.

 

[Alex Blakenburg]

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.

 

[Stacker]

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

 

[Stacker]

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.

https://imgur.com/N9FvyhS

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

https://imgur.com/6Jnxtap

When I click combine it shows this

https://imgur.com/YeMcjUC

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.

https://imgur.com/YeMcjUC

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

https://imgur.com/YeMcjUC

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.

 

[Alex Blakenburg]

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
    
    

    = 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.