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

S

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

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?
 
Sort by date Sort by votes
Sorry. I sent you a screenshot of the final query. I have no idea what XL2bb is.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
Click to expand...
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?
 
Upvote 0
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

NHSNumberMRNDateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTimeColumn1
2468918/06/1935WHITE - BRITISH123 BUSY LANEWK12 123York01/01/202111/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN
2469015/09/1944WHITE - BRITISH456 Bee DriveWK12 124York01/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN
2469118/06/1936Black124 BUSY LANEWK12 125Texas01/01/202111/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
2469215/09/1945Black457 Bee DriveWK12 126Atlanta01/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
2469318/06/1937Black125 BUSY LANEWK12 127London01/01/202111/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
 
Upvote 0
Mapping Table

BEFOREAFTER
NumberNumber
DOBDOB
EthnicityEthnicity (from Hospital)
EthnicCategoryEthnicity (from Hospital)
Ethnicity (from Hospital)Ethnicity (from Hospital)
PatientAddressPatientAddress
PatientAddress1PatientAddress
PostcodePostcode
PatientPostcodePostcode
HospitalHospital
DateAdmittedDateAdmitted
DateTestedPositiveDate tested Positive
DateDischargedDate discharged (if applicable)
DateDischarged (if applicable)Date discharged (if applicable)
AddressDischargedToDischarge Destination
DischargeDestinationDischarge Destination
Patient Currently Admitted At Report Generation TimeInpatientAdmissionTime
DateTestedPositiveInpatientDischargeTime


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.xlsx
ABCDEFGHIJKLMN
1NHS NumberDOBEthnicityPatientAddress1PatientAddress2PatientAddress3PatientAddress4PatientPostcodeHospitalDateAdmittedDateTestedPositiveDateDischarged (if applicable)DischargeDestinationPatient Currently Admitted At Report Generation Time
212318/06/1935WHITE - BRITISH123 BUSY LANEDEWSBURYYorkBlahWK12 123SACRED HEART01/01/202111/01/202112/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN
312415/09/1944WHITE - BRITISH456 Bee DriveBridgeYorkBlah BlahWK12 124ARKHAM01/01/202101/01/202104/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHN
412518/06/1936Black124 BUSY LANEDEWSBURYTexasBlahWK12 125SACRED HEART01/01/202111/01/202212/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH
512615/09/1945Black457 Bee DriveBridgeAtlantaBlah BlahWK12 126ARKHAM01/01/202101/01/202104/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTH
612718/06/1937Black125 BUSY LANEDEWSBURYLondonBlahWK12 127SACRED HEART01/01/202111/01/202312/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1


Reddit 2

Reddit2.xlsx
CDEFGHIJKLMNOP
1DateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTimeColumn1
218/06/1935WHITE - BRITISH123 BUSY LANEWK12 123Sacred Heart24/09/202101/01/202111/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN
315/09/1944WHITE - BRITISH456 Bee DriveWK12 124St Judes25/09/202101/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTHN
418/06/1936Black124 BUSY LANEWK12 125Mash26/09/202101/01/202111/01/2022NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
515/09/1945Black457 Bee DriveWK12 126Chicago Hope27/09/202101/01/202101/01/2021NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
618/06/1937Black125 BUSY LANEWK12 127St Elmo28/09/202101/01/202111/01/2023NOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1


Is this what you mean?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thanks. Here you go:

BEFOREAFTER
NumberNumber
DOBDOB
EthnicityEthnicity (from Hospital)
EthnicCategoryEthnicity (from Hospital)
Ethnicity (from Hospital)Ethnicity (from Hospital)
PatientAddressPatientAddress
PatientAddress1PatientAddress
PostcodePostcode
PatientPostcodePostcode
HospitalHospital
DateAdmittedDateAdmitted
DateTestedPositiveDate tested Positive
DateDischargedDate discharged (if applicable)
DateDischarged (if applicable)Date discharged (if applicable)
AddressDischargedToDischarge Destination
DischargeDestinationDischarge Destination
Patient Currently Admitted At Report Generation TimeInpatientAdmissionTime


Reddit2.xlsx
ABCDEFGHIJKLMNO
1NHSNumberMRNDateOfBirthEthnicCategoryPatientAddressPostcodeHospitalDateAdmittedDateTestedPositiveDateDischargedAddressDischargedToPostcodeDischargedToPatientCategoryInpatientAdmissionTimeInpatientDischargeTime
22468918/06/1935WHITE - BRITISH123 BUSY LANEWK12 123Sacred Heart24/09/202101/01/2021123 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTH
32469015/09/1944WHITE - BRITISH456 Bee DriveWK12 124St Judes25/09/202101/01/2021111 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNNOT APPLICABLE -PATIENT DIED/STILL BIRTH
42469118/06/1936Black124 BUSY LANEWK12 125Mash26/09/202101/01/2021333 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
52469215/09/1945Black457 Bee DriveWK12 126Chicago Hope27/09/202101/01/2021444 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
62469318/06/1937Black125 BUSY LANEWK12 127St Elmo28/09/202101/01/2021555 RandomNOT APPLICABLE -PATIENT DIED/STILL BIRTHNOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1



Reddit2.xlsx
P
2N
Sheet1
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
In Power Query, when combining/appending multiple tables is there a way to add a column which will give the name of file/table the data is from.
Replies
2
Views
393
Alex Blakenburg
A
C
  • Solved
Help me Convert, Merge and Transpose a pdf table into a singular row, and then repeat that step ad nauseam to create a database
Replies
5
Views
630
Clonk92
C
M
  • Question Question
Power Query Appending new CSV files to an EXISTING Excel table
2
Replies
12
Views
7K
DoomBlasZt
D
S
  • Question Question
Power query - combine 2 tables from PDF file into one page
Replies
1
Views
2K
sara121
S
S
  • Question Question
Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=Type
Replies
0
Views
8K
Stacker
S

Forum statistics

Threads
1,224,830
Messages
6,181,229
Members
453,026
Latest member
cknader

Share this page

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