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



## Stacker (Jul 17, 2021)

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 (Jul 18, 2021)

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


----------



## Alex Blakenburg (Jul 18, 2021)

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 (Jul 18, 2021)

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.


Alex Blakenburg said:


> 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 (Jul 18, 2021)

Stacker said:


> They have different table names, which I think the start of my video shows.





Stacker said:


> 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 (Jul 18, 2021)

Alex Blakenburg said:


> 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 (Jul 18, 2021)

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 (Jul 18, 2021)

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 (Jul 18, 2021)

Alex Blakenburg said:


> 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


The matching columns in the respective files are matched onto where they are onto the template
The data in the two files is appended onto the template file with the second files data directly following the first.


----------



## Alex Blakenburg (Jul 19, 2021)

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.


----------



## Stacker (Jul 17, 2021)

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 (Jul 19, 2021)

THa


Alex Blakenburg said:


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


Thank you for your response. I get two files daily and they are in a slightly different format to the final output setting that is the template.

Here is what the final heading would look like and it is what is saved in template




NHS NumberDOBEthnicity (from Hospital)EthnicityPatientAddress1PostcodePatient UPRNPatient TertiaryClassificationHospitalDateAdmittedDate tested PositiveDate discharged (if applicable)Discharge DestinationInpatientAdmissionTimeInpatientDischargeTimeTrustCombinedAdmitted_dateTested_dateDischarged_date




Alex Blakenburg said:


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












One file will be



NHS NumberDOBEthnicityPatientAddress1PatientAddress2PatientAddress3PatientAddress4PatientPostcodeHospitalDateAdmittedDateTestedPositiveDateDischarged (if applicable)DischargeDestinationPatient Currently Admitted At Report Generation Time



and the other will be these headings.

NHSNumber    MRN    DateOfBirth    EthnicCategory    PatientAddress    Postcode    Hospital    DateAdmitted    DateTestedPositive    DateDischarged    AddressDischargedTo    PostcodeDischargedTo    PatientCategory    InpatientAdmissionTime    InpatientDischargeTime

As you can see they are mostly consistent though slightly different. I'll go through your steps now.


----------



## Alex Blakenburg (Jul 19, 2021)

In the Transform Sample File > Home > Advanced Editor
Try this:-


```
let
    Source = Excel.Workbook(Parameter1, null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    Custom1 = Table.TransformColumnNames (Table1_Table, each List.Accumulate(Table.ToRecords(Table23),_,(state,current) => Text.Replace(state,current[BEFORE],current[AFTER])))
in
    Custom1
```


----------



## Stacker (Jul 19, 2021)

What should I replace Parameter 1 with?


----------



## Alex Blakenburg (Jul 19, 2021)

I thought it was in your original code, so if you continued on from where you were it should accept it.
If not, it should be your original source line with a comma added at the end.
The the other lines overwrite everything from there to the end (including replacing the current "in" lines)


----------



## Stacker (Jul 21, 2021)

Here is the mapping table I used


BEFOREAFTERNHS NumberNHS NumberDOBDOBEthnicityEthnicity (from Hospital)PatientAddress1PatientAddress1PatientPostcodePostcodeHospitalHospitalDateAdmittedDateAdmittedDateTestedPositiveDate tested PositiveDateDischarged (if applicable)Date discharged (if applicable)DischargeDestinationDischarge DestinationNHSNumberNHS NumberDateOfBirthDOBEthnicCategoryEthnicity (from Hospital)PatientAddressPatientAddress1PostcodePostcodeHospitalHospital

I keep getting errors. and this is the transformation I used

= Table.TransformColumnNames(Table1_Table, each List.Accumulate(Table.ToRecords(Table23), _ , (state, current)=>Text.Replace((state), current [BEFORE],current[AFTER]) ))

Table1 is the title  of the tables i want to join
Table23 is what i saved the mapping table as


----------



## Alex Blakenburg (Jul 21, 2021)

Stacker said:


> I keep getting errors. and this is the transformation I used



The only issue I can see with you mapping table is 
• that Hospital is being mapped twice. So you might want to remove that.
• I think these might need to be added


DateDischargedDate discharged (if applicable)Reddit2AddressDischargedToDischarge DestinationReddit2

Neither of the above seem to be causing a major error though.
Where is the error occurring and what is the M Code for the error step ?
If it is the last changed step how does it look if you go one step before that (probably the "Expanded Table Column1" step)


----------



## Stacker (Jul 21, 2021)

= Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"NHS Number", Int64.Type}, {"DOB", type date}, {"Ethnicity", type text}, {"PatientAddress1", type text}, {"PatientAddress2", type text}, {"Column1", type text}, {"PatientAddress3", type text}, {"PatientAddress4", type any}, {"PatientPostcode", type text}, {"Hospital", type text}, {"DateAdmitted", type text}, {"DateTestedPositive", type date}, {"DateDischarged (if applicable)", type date}, {"DischargeDestination", type text}, {"Patient Currently Admitted At Report Generation Time", type text}})

Expression.Error: The column 'Ethnicity' of the table wasn't found. Details:    Ethnicity


Alex Blakenburg said:


> The only issue I can see with you mapping table is
> • that Hospital is being mapped twice. So you might want to remove that.
> • I think these might need to be added
> 
> ...


----------



## Alex Blakenburg (Jul 21, 2021)

Changed Type is the only step that hard codes the column names. If you have changed any of the values in the AFTER column or added new records to the mapping table, that line is going to fail.
You simply need to

Delete that step
While on what is now the last step "Expanded Table Column1"
Click in the grid > hit ctrl+A to select ALL then Transform > Detect data type
Then review the data types and on a column by column basis correct any that are wrong ie Date / Time and you want Date, Whole no and you want decimals.

One of the reasons it is currently failing is that there must have been an AFTER value of Ethnicity in the Mapping table.  This is not the case in the current Mapping table. The current equivalent is "Ethnicity (from Hospital)"


----------



## Stacker (Jul 21, 2021)

"Ethnicity (from Hospital)" is what I want the after value to be. What I find weird is how only half of the values are appending. Like some are but some aren't. I think all of the headings are transformed . It's just not all of the files are being appended. For example



https://imgur.com/vNNNgVO


As you can see not appended. 

However if you look here:



https://imgur.com/N0iKN9m


Hospital and DateAdmitted are appended. If I scroll right, I can see the rest of the appended file.



https://imgur.com/vgq7Tjf



So the problem is definitely in the transformation. I just am not sure how to fix it.


----------



## Alex Blakenburg (Jul 21, 2021)

Stacker said:


> So the problem is definitely in the transformation. I just am not sure how to fix it.




I am not sure that it is. I had a lot of trouble with you Reddit2 file in that the data did not align with the column headings.
I put it down to the fact that you did not use XL2BB to copy in the data but used a straight copy paste.
Ideally review your reddit2 file for proper alignment. Once you have done that if you have not found the problem feel free to send me an XL2BB copy.
If that is really not possible then try another copy paste since I only seem to have had the issue with that data (Reddit1 & Mapping table were fine)


----------



## Stacker (Jul 17, 2021)

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 (Jul 21, 2021)

Sorry. I sent you a screenshot of the final query.  I have no idea what XL2bb is.


----------



## Alex Blakenburg (Jul 21, 2021)

Your picture is consistent with the copy paste you sent me.
You have several date columns with text data instead of dares. Your reddit2 file is corrupt


----------



## Stacker (Jul 21, 2021)

Alex Blakenburg said:


> Your picture is consistent with the copy paste you sent me.
> You have several date columns with text data instead of dares. Your reddit2 file is corrupt


I was wondering have you tried it with the dummy data I sent you with two seperate files and  my mapping table? Were you able to pull it off?


----------



## Alex Blakenburg (Jul 21, 2021)

The only dummy data I have seen for Reddit2 is the below and you can see that

DateAdmitted - contains text
AddressDischargedTo - contains a date
Your original static screenshots of Reddit2 show the same thing.
I still maintain that your Reddit2 file is corrupt the data does not line up with the headings.
This is causing data type inconsistencies causing some nulls and explaining why you think PQ is putting things in the wrong columns whereas PQ is only putting it where the Reddit2 file has it positioned.

PS: In terms of XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in


NHSNumberMRNDateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTimeColumn12468918/06/1935WHITE - BRITISH123 BUSY LANEWK12 123York01/01/202111/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN2469015/09/1944WHITE - BRITISH456 Bee DriveWK12 124York01/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN2469118/06/1936Black124 BUSY LANEWK12 125Texas01/01/202111/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH2469215/09/1945Black457 Bee DriveWK12 126Atlanta01/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH2469318/06/1937Black125 BUSY LANEWK12 127London01/01/202111/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH


----------



## Stacker (Jul 23, 2021)

Mapping Table

BEFOREAFTERNumberNumberDOBDOBEthnicityEthnicity (from Hospital)EthnicCategoryEthnicity (from Hospital)Ethnicity (from Hospital)Ethnicity (from Hospital)PatientAddressPatientAddressPatientAddress1PatientAddressPostcodePostcodePatientPostcodePostcodeHospitalHospitalDateAdmittedDateAdmittedDateTestedPositiveDate tested PositiveDateDischargedDate discharged (if applicable)DateDischarged (if applicable)Date discharged (if applicable)AddressDischargedToDischarge DestinationDischargeDestinationDischarge DestinationPatient Currently Admitted At Report Generation TimeInpatientAdmissionTimeDateTestedPositiveInpatientDischargeTime

Template from which I hope to append things

NHS NumberDOBEthnicity (from Hospital)EthnicityPatientAddress1PostcodePatient UPRNPatient TertiaryClassificationHospitalDateAdmittedDate tested PositiveDate discharged (if applicable)Discharge DestinationInpatientAdmissionTimeInpatientDischargeTimeTrustCombinedAdmitted_dateTested_dateDischarged_date

File 1

Reddit1.xlsxABCDEFGHIJKLMN1NHS NumberDOBEthnicityPatientAddress1PatientAddress2PatientAddress3PatientAddress4PatientPostcodeHospitalDateAdmittedDateTestedPositiveDateDischarged (if applicable)DischargeDestinationPatient Currently Admitted At Report Generation Time212318/06/1935WHITE - BRITISH123 BUSY LANEDEWSBURYYorkBlahWK12 123SACRED HEART01/01/202111/01/202112/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN312415/09/1944WHITE - BRITISH456 Bee DriveBridgeYorkBlah BlahWK12 124ARKHAM01/01/202101/01/202104/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN412518/06/1936Black124 BUSY LANEDEWSBURYTexasBlahWK12 125SACRED HEART01/01/202111/01/202212/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH512615/09/1945Black457 Bee DriveBridgeAtlantaBlah BlahWK12 126ARKHAM01/01/202101/01/202104/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH612718/06/1937Black125 BUSY LANEDEWSBURYLondonBlahWK12 127SACRED HEART01/01/202111/01/202312/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHSheet1

Reddit 2

Reddit2.xlsxCDEFGHIJKLMNOP1DateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTimeColumn1218/06/1935WHITE - BRITISH123 BUSY LANEWK12 123Sacred Heart24/09/202101/01/202111/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN315/09/1944WHITE - BRITISH456 Bee DriveWK12 124St Judes25/09/202101/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN418/06/1936Black124 BUSY LANEWK12 125Mash26/09/202101/01/202111/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH515/09/1945Black457 Bee DriveWK12 126Chicago Hope27/09/202101/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH618/06/1937Black125 BUSY LANEWK12 127St Elmo28/09/202101/01/202111/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTHSheet1

Is this what you mean?


----------



## Alex Blakenburg (Jul 23, 2021)

The XL2BB themselves are fine except that you have* missed out Columns A & B in Reddit2.*

Data wise it still has some obvious data issues.

Mapping Table
DateTestedPositive in column A of the mapping table *twice*

Reddit2
AddressDischargeTo - Column K - has dates in the field the heading indicates it should be an address
Column P has a heading of Column1 and has a couple of values of "N". Should there be any data in column P and if so what should the heading be.


----------



## Stacker (Jul 23, 2021)

For reasons, I don't know the Xl2bb is not now working.


----------



## Alex Blakenburg (Jul 23, 2021)

Do you still have it on your toolbar ?
If not have a look under Developer > Excel Add-Ins (Gear icon) and see if it is there and ticked.

If you figure out what is wrong with Reddit2 and send me new data I can run it through my model and compare. Most likely tomorrow since it is quite late here.

Also please confirm that you have changed your Reddit1 & 2 tables from Table1 to Table3 and I will make the same changes at my end, so it matches what your code should look like.


----------



## Stacker (Jul 23, 2021)

Thanks. Here you go:

BEFOREAFTERNumberNumberDOBDOBEthnicityEthnicity (from Hospital)EthnicCategoryEthnicity (from Hospital)Ethnicity (from Hospital)Ethnicity (from Hospital)PatientAddressPatientAddressPatientAddress1PatientAddressPostcodePostcodePatientPostcodePostcodeHospitalHospitalDateAdmittedDateAdmittedDateTestedPositiveDate tested PositiveDateDischargedDate discharged (if applicable)DateDischarged (if applicable)Date discharged (if applicable)AddressDischargedToDischarge DestinationDischargeDestinationDischarge DestinationPatient Currently Admitted At Report Generation TimeInpatientAdmissionTime

Reddit2.xlsxABCDEFGHIJKLMNO1NHSNumberMRNDateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTime22468918/06/1935WHITE - BRITISH123 BUSY LANEWK12 123Sacred Heart24/09/202101/01/2021123 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTH32469015/09/1944WHITE - BRITISH456 Bee DriveWK12 124St Judes25/09/202101/01/2021111 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTH42469118/06/1936Black124 BUSY LANEWK12 125Mash26/09/202101/01/2021333 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH52469215/09/1945Black457 Bee DriveWK12 126Chicago Hope27/09/202101/01/2021444 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH62469318/06/1937Black125 BUSY LANEWK12 127St Elmo28/09/202101/01/2021555 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTHSheet1


Reddit2.xlsxP2NSheet1


----------



## Alex Blakenburg (Jul 23, 2021)

I will run it through tomorrow. 
If you run the cleaned data version through your model and send me where you still have issues, I can compare it to my results when I run it through mine.


----------



## Stacker (Jul 17, 2021)

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 (Jul 23, 2021)

Here is the transformation of the sample file I did

= Table.TransformColumnNames(Table1_Table, each List.Accumulate(Table.ToRecords(Table23), _ , (state, current)=>Text.Replace((state), current [BEFORE],current[AFTER]) ))

I get the subsequent error

Expression.Error: The column 'Ethnicity' of the table wasn't found.
Details:
    Ethnicity

I played around with the final reddit query and it produces this



Book1ABCDEFGHIJKLMNO1NHS NumberDOBEthnicityPatientAddress1PatientAddress2Column1PatientAddress3PatientAddress4PatientPostcodeHospitalDateAdmittedDateTestedPositiveDateDischarged (if applicable)DischargeDestinationPatient Currently Admitted At Report Generation Time212318/06/1935WHITE - BRITISH123 BUSY LANEDEWSBURYYorkWK12 123SACRED HEART01/01/202111/01/202112/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN312415/09/1944WHITE - BRITISH456 Bee DriveBridgeYorkWK12 124ARKHAM01/01/202101/01/202104/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN412518/06/1936Black124 BUSY LANEDEWSBURYTexasWK12 125SACRED HEART01/01/202111/01/202212/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH512615/09/1945Black457 Bee DriveBridgeAtlantaWK12 126ARKHAM01/01/202101/01/202104/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH612718/06/1937Black125 BUSY LANEDEWSBURYLondonWK12 127SACRED HEART01/01/202111/01/202312/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTH7NSacred Heart24/09/20218NSt Judes25/09/20219Mash26/09/202110Chicago Hope27/09/202111St Elmo28/09/202112Sheet2


----------



## Alex Blakenburg (Jul 25, 2021)

I have abandoned using Leila's method. For your purposes it has a number of issues.

It has a bug in it. It is using an unqualified Replace, and in your scenario,
in Reddit1 you only want PatientAddress1 mapped but the Reddit2 mapping of PatientAddress is being applied to *Reddit1's PatientAddress1, 2, 3 & 4*


The 2nd issue is, I assume that you want all column in your template in the output.
This is easier to do using a different approach.
Can you review your Template, these columns seem to be duplicates:-

DateAdmitted & Admitted_date
Date discharged (if applicable) & Discharged_date
These could be different but you need to check
Date tested Positive & Tested_date
If you are happy to change tack I can try and talk you through it but it does have quite a few moving parts, although conceptually simpler.

Essentially

Load from Current Workbook - Mapping table
Load from Current Workbook -Template
Load from folder both Reddit files
Create an index
Unpivot it
Map the columns
Pivot it back (using the index)

Using the Template Query Append Reddit file query
Filter out the single template row loaded


----------



## Stacker (Jul 25, 2021)

Alex Blakenburg said:


> I have abandoned using Leila's method. For your purposes it has a number of issues.
> 
> It has a bug in it. It is using an unqualified Replace, and in your scenario,
> in Reddit1 you only want PatientAddress1 mapped but the Reddit2 mapping of PatientAddress is being applied to *Reddit1's PatientAddress1, 2, 3 & 4*
> ...


My supervisor said the duplicate columns are needed for other things and it's intentional.  When you say Load from Current Workbook- Mapping Table do you mean create connections or Data- Get Data- From Workbook- Mapping Table table- as connection? Or do you mean actually load it onto a sheet? Anyhow, I created an index column, unpivoted said column but I am a little unsure how to map said columns


----------



## Alex Blakenburg (Jul 25, 2021)

Mapping table
Get Data > from range.
Connection only.

Reddit files
Get Data > from folder
Connection only
Add index column

Merge > Left outer join to mapping query. 
Join on BEFORE column
Pull AFTER value into the query.


----------



## Stacker (Jul 25, 2021)

Alex Blakenburg said:


> Mapping table
> Get Data > from range.
> Connection only.
> 
> ...


I tried to merge the index column and ok is greyed out but the other columns aren't, Is it cos  I combined the two reddit files in the previous step?


----------



## Stacker (Jul 25, 2021)

This video might be helpful as well.


----------



## Alex Blakenburg (Jul 25, 2021)

I am going to have to wind this up.

Here is my output. I can give you the code to make to do it but it is multiple queries.
You would need to be prepared to use these table names:

tblMapping - Mapping Table
tblTemplate - Template Table - NHS Number on first line called "TemplateRow1"
Reddit Files - Table1
The assuming is that the tblMapping, tblTemplate and the output is all in the same workbook (which is also the workbook with the queries).

I have not used any manually modified formulas, to produce the below it has been via the ribbon interface.

Main Incl Mapping Param1 v06.xlsxIJKLMNOPQRSTUVWXYZAAAB17NHS NumberDOBEthnicity (from Hospital)EthnicityPatientAddress1PostcodePatient UPRNPatient TertiaryClassificationHospitalDateAdmittedDate tested PositiveDate discharged (if applicable)Discharge DestinationInpatientAdmissionTimeInpatientDischargeTimeTrustCombinedAdmitted_dateTested_dateDischarged_date1812318/06/1935WHITE - BRITISH123 BUSY LANEWK12 123SACRED HEART1/01/202111/01/202112/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH1912415/09/1944WHITE - BRITISH456 Bee DriveWK12 124ARKHAM1/01/20211/01/20214/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH2012518/06/1936Black124 BUSY LANEWK12 125SACRED HEART1/01/202111/01/202212/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH2112615/09/1945Black457 Bee DriveWK12 126ARKHAM1/01/20211/01/20214/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH2212718/06/1937Black125 BUSY LANEWK12 127SACRED HEART1/01/202111/01/202312/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH232468918/06/1935WHITE - BRITISH123 BUSY LANEWK12 123Sacred Heart24/09/20211/01/2021123 Random242469015/09/1944WHITE - BRITISH456 Bee DriveWK12 124St Judes25/09/20211/01/2021111 Random252469118/06/1936Black124 BUSY LANEWK12 125Mash26/09/20211/01/2021333 Random262469215/09/1945Black457 Bee DriveWK12 126Chicago Hope27/09/20211/01/2021444 Random272469318/06/1937Black125 BUSY LANEWK12 127St Elmo28/09/20211/01/2021555 RandomMapping


----------



## Stacker (Jul 25, 2021)

Alex Blakenburg said:


> I am going to have to wind this up.
> 
> Here is my output. I can give you the code to make to do it but it is multiple queries.
> You would need to be prepared to use these table names:
> ...



Thank you, how do i implement the code?


----------



## Alex Blakenburg (Jul 25, 2021)

This is how my queries are organised and named.
For the reddit get from folder. You are best off creating the structure first by importing the file manually.
Filtering on files starting with Reddit and exploding Binary.

If you start with a clean slate hopefully the files will come out with the same names as the below for the Transform files.

Create a blank query for tblMapping & FinalRptTemplateFormat and drop in the code.

Also drop in the code for the other 2 shown below.

For you to minimise changes use the table names I mentioned previously.






*tblMapping*


```
let
    Source = Excel.CurrentWorkbook(){[Name="tblMapping"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BEFORE", type text}, {"AFTER", type text}})
in
    #"Changed Type"
```

*Transform Sample File*


```
let
    Source = Excel.Workbook(Parameter1, null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Added Index" = Table.AddIndexColumn(Table1_Table, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, tblMapping, {"BEFORE"}, "tblMapping", JoinKind.LeftOuter),
    #"Expanded tblMapping" = Table.ExpandTableColumn(#"Merged Queries", "tblMapping", {"AFTER"}, {"AFTER"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded tblMapping", each ([AFTER] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[AFTER]), "AFTER", "Value")
in
    #"Pivoted Column"
```

*CombinedRedditFiles - You will need to change the source folder here*


```
let
    Source = Folder.Files("C:\Users\QuestionNaming"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Reddit")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name", "Index"})
in
    #"Removed Columns"
```


*FinalRptTemplateFormat*


```
let
    Source = Excel.CurrentWorkbook(){[Name="tblTemplate"]}[Content],
    #"Appended Query" = Table.Combine({Source, CombinedRedditFiles}),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([NHS Number] <> "TemplateRow1")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NHS Number", Int64.Type}, {"DOB", type date}, {"Ethnicity (from Hospital)", type text}, {"Ethnicity", type any}, {"PatientAddress1", type text}, {"Postcode", type text}, {"Patient UPRN", type any}, {"Patient TertiaryClassification", type any}, {"Hospital", type text}, {"DateAdmitted", type date}, {"Date tested Positive", type date}, {"Date discharged (if applicable)", type date}, {"Discharge Destination", type text}, {"InpatientAdmissionTime", type text}, {"InpatientDischargeTime", type any}, {"Trust", type any}, {"Combined", type any}, {"Admitted_date", type any}, {"Tested_date", type any}, {"Discharged_date", type any}})
in
    #"Changed Type"
```


----------



## Alex Blakenburg (Jul 25, 2021)

*My Template table looks like this:*

Main Incl Mapping Param1 v06.xlsxABCDEFGHIJKLMNOPQRST3NHS NumberDOBEthnicity (from Hospital)EthnicityPatientAddress1PostcodePatient UPRNPatient TertiaryClassificationHospitalDateAdmittedDate tested PositiveDate discharged (if applicable)Discharge DestinationInpatientAdmissionTimeInpatientDischargeTimeTrustCombinedAdmitted_dateTested_dateDischarged_date4TemplateRow1Template

*Your mapping table also still has issues to use this:-*

Main Incl Mapping Param1 v06.xlsxAB1BEFOREAFTER2NHS NumberNHS Number3DOBDOB4EthnicityEthnicity (from Hospital)5PatientAddressPatientAddress16PatientAddress1PatientAddress17PatientPostcodePostcode8HospitalHospital9DateAdmittedDateAdmitted10DateTestedPositiveDate tested Positive11DateDischarged (if applicable)Date discharged (if applicable)12DischargeDestinationDischarge Destination13DateOfBirthDOB14EthnicCategoryEthnicity (from Hospital)15PostcodePostcode16DateDischargedDate discharged (if applicable)17AddressDischargedToDischarge Destination18Patient Currently Admitted At Report Generation TimeInpatientAdmissionTime19NHSNumberNHS Number20Patient UPRNPatient UPRN21Patient TertiaryClassificationPatient TertiaryClassification22InpatientDischargeTimeInpatientDischargeTime23TrustTrust24CombinedCombined25Admitted_dateAdmitted_date26Tested_dateTested_date27Discharged_dateDischarged_dateMapping


----------



## Stacker (Jul 17, 2021)

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 (Jul 25, 2021)

Alex Blakenburg said:


> This is how my queries are organised and named.
> For the reddit get from folder. You are best off creating the structure first by importing the file manually.
> Filtering on files starting with Reddit and exploding Binary.
> 
> ...


 
So I started with a blank sheet. 


Alex Blakenburg said:


> This is how my queries are organised and named.
> For the reddit get from folder. You are best off creating the structure first by importing the file manually.
> Filtering on files starting with Reddit and exploding Binary.
> 
> ...



Sorry I just wanna clarify something. I copied and pasted the template and mapping table below using the code below. Saved the tab as "tblMapping"

Data> Get Data>> From other sources>>  Blank Query>> Advanced Editor

Ran the tblMapping query and it said :

"Expression.Error: We couldn't find an Excel table named 'tblMapping'.
Details:
    tblMapping"

Did I do it wrong?


----------



## Alex Blakenburg (Jul 25, 2021)

If by tab you mean sheet name then yes.
If not already an excel table make it a table.
Then click inside the table > click on tab far right Query Design > far left put tblMapping into the query name box


----------



## Stacker (Jul 25, 2021)

Alex Blakenburg said:


> If by tab you mean sheet name then yes.
> If not already an excel table make it a table.
> Then click inside the table > click on tab far right Query Design > far left put tblMapping into the query name box


It still says the same "
Expression.Error: The column 'BEFORE' of the table wasn't found.
Details:
    BEFORE"


----------



## Alex Blakenburg (Jul 25, 2021)

Then simply click on the table, get data from > range to create tblMapping query
If the query name is not tblMapping change it to that


----------



## Stacker (Jul 25, 2021)

Sorry, I feel stupid but where am I going wrong?






I think I set it up as it should  be but I am not sure how to use the above queries so it loads.


----------



## Alex Blakenburg (Jul 25, 2021)

Are you able to send me a copy of your file using Dropbox or onedrive or SharePoint ?


----------



## Stacker (Jul 25, 2021)

Alex Blakenburg said:


> Are you able to send me a copy of your file using Dropbox or onedrive or SharePoint ?


I think the problem is that I don't know what to do rather than there's something wrong with my file. I made a worksheet with all of those files as connections and saved as tables hence why I was going through the saved tables. I go to data> get data> from other sources> blank query> advanced editor and then copy and paste the above queries but i think i am missing a step.


----------



## Alex Blakenburg (Jul 25, 2021)

If the only issue is loading it back to the spreadsheet, then when you are in Excel and have the queries and connections pane on the right 
> right click on the final query (which I have called FinalRptTemplateFormat) 
> select Close and Load to
> select table and either New Worksheet OR existing Worksheet giving a location


----------



## Alex Blakenburg (Jul 25, 2021)

If you run the final report in PQ are you getting  something that looks right.


----------



## Stacker (Aug 3, 2021)

So yeah I finally worked it out. 

1) First things first, create a mapping table and make a connection out of it.

Get data>From file> worksheet> mapping table file> click mapping table> create connection

2) Get data> from file> sample file> pick a folder> pick a file

3) Click on the "Transform sample file"> add index> unpivot other column> merge queries> click attribute> click mapping table> then before> join left outer>

4) click on mapping column> after> remove null> remove attribute>> organise index>>pivot after column> values column is value> advanced option> don' aggregate

5) Go to the combination query> removed changed type  in applied steps

and voila. Thanks for the help.


----------



## Stacker (Jul 17, 2021)

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?


----------



## Alex Blakenburg (Aug 3, 2021)

Thank you for the feedback glad you got it working in the end.


----------

