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]

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

 

[Alex Blakenburg]

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]

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]

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

NHSNumber

MRN

DateOfBirth

EthnicCategory

PatientAddress

Postcode

Hospital

DateAdmitted

DateTestedPositive

DateDischarged

AddressDischargedTo

PostcodeDischargedTo

PatientCategory

InpatientAdmissionTime

InpatientDischargeTime

Column1

24689

18/06/1935

WHITE - BRITISH

123 BUSY LANE

WK12 123

York

01/01/2021

11/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

N

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

N

24690

15/09/1944

WHITE - BRITISH

456 Bee Drive

WK12 124

York

01/01/2021

01/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

N

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

N

24691

18/06/1936

Black

124 BUSY LANE

WK12 125

Texas

01/01/2021

11/01/2022

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

24692

15/09/1945

Black

457 Bee Drive

WK12 126

Atlanta

01/01/2021

01/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

24693

18/06/1937

Black

125 BUSY LANE

WK12 127

London

01/01/2021

11/01/2023

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

 

[Stacker]

Mapping Table

BEFORE	AFTER
Number	Number
DOB	DOB
Ethnicity	Ethnicity (from Hospital)
EthnicCategory	Ethnicity (from Hospital)
Ethnicity (from Hospital)	Ethnicity (from Hospital)
PatientAddress	PatientAddress
PatientAddress1	PatientAddress
Postcode	Postcode
PatientPostcode	Postcode
Hospital	Hospital
DateAdmitted	DateAdmitted
DateTestedPositive	Date tested Positive
DateDischarged	Date discharged (if applicable)
DateDischarged (if applicable)	Date discharged (if applicable)
AddressDischargedTo	Discharge Destination
DischargeDestination	Discharge Destination
Patient Currently Admitted At Report Generation Time	InpatientAdmissionTime
DateTestedPositive	InpatientDischargeTime

Template from which I hope to append things

NHS Number	DOB	Ethnicity (from Hospital)	Ethnicity	PatientAddress1	Postcode	Patient UPRN	Patient TertiaryClassification	Hospital	DateAdmitted	Date tested Positive	Date discharged (if applicable)	Discharge Destination	InpatientAdmissionTime	InpatientDischargeTime	Trust	Combined	Admitted_date	Tested_date	Discharged_date

File 1

Reddit1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NHS Number	DOB	Ethnicity	PatientAddress1	PatientAddress2	PatientAddress3	PatientAddress4	PatientPostcode	Hospital	DateAdmitted	DateTestedPositive	DateDischarged (if applicable)	DischargeDestination	Patient Currently Admitted At Report Generation Time
2	123	18/06/1935	WHITE - BRITISH	123 BUSY LANE	DEWSBURY	York	Blah	WK12 123	SACRED HEART	01/01/2021	11/01/2021	12/01/2021	NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N
3	124	15/09/1944	WHITE - BRITISH	456 Bee Drive	Bridge	York	Blah Blah	WK12 124	ARKHAM	01/01/2021	01/01/2021	04/01/2021	NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N
4	125	18/06/1936	Black	124 BUSY LANE	DEWSBURY	Texas	Blah	WK12 125	SACRED HEART	01/01/2021	11/01/2022	12/01/2022	NOT APPLICABLE -PATIENT DIED/STILL BIRTH
5	126	15/09/1945	Black	457 Bee Drive	Bridge	Atlanta	Blah Blah	WK12 126	ARKHAM	01/01/2021	01/01/2021	04/01/2022	NOT APPLICABLE -PATIENT DIED/STILL BIRTH
6	127	18/06/1937	Black	125 BUSY LANE	DEWSBURY	London	Blah	WK12 127	SACRED HEART	01/01/2021	11/01/2023	12/01/2023	NOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1

Reddit 2

Reddit2.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	DateOfBirth	EthnicCategory	PatientAddress	Postcode	Hospital	DateAdmitted	DateTestedPositive	DateDischarged	AddressDischargedTo	PostcodeDischargedTo	PatientCategory	InpatientAdmissionTime	InpatientDischargeTime	Column1
2	18/06/1935	WHITE - BRITISH	123 BUSY LANE	WK12 123	Sacred Heart	24/09/2021		01/01/2021	11/01/2021		NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N	NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N
3	15/09/1944	WHITE - BRITISH	456 Bee Drive	WK12 124	St Judes	25/09/2021		01/01/2021	01/01/2021		NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N	NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N
4	18/06/1936	Black	124 BUSY LANE	WK12 125	Mash	26/09/2021		01/01/2021	11/01/2022		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
5	15/09/1945	Black	457 Bee Drive	WK12 126	Chicago Hope	27/09/2021		01/01/2021	01/01/2021		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
6	18/06/1937	Black	125 BUSY LANE	WK12 127	St Elmo	28/09/2021		01/01/2021	11/01/2023		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1

Is this what you mean?

 

[Alex Blakenburg]

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]

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

 

[Alex Blakenburg]

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]

Thanks. Here you go:

BEFORE	AFTER
Number	Number
DOB	DOB
Ethnicity	Ethnicity (from Hospital)
EthnicCategory	Ethnicity (from Hospital)
Ethnicity (from Hospital)	Ethnicity (from Hospital)
PatientAddress	PatientAddress
PatientAddress1	PatientAddress
Postcode	Postcode
PatientPostcode	Postcode
Hospital	Hospital
DateAdmitted	DateAdmitted
DateTestedPositive	Date tested Positive
DateDischarged	Date discharged (if applicable)
DateDischarged (if applicable)	Date discharged (if applicable)
AddressDischargedTo	Discharge Destination
DischargeDestination	Discharge Destination
Patient Currently Admitted At Report Generation Time	InpatientAdmissionTime

Reddit2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NHSNumber	MRN	DateOfBirth	EthnicCategory	PatientAddress	Postcode	Hospital	DateAdmitted	DateTestedPositive	DateDischarged	AddressDischargedTo	PostcodeDischargedTo	PatientCategory	InpatientAdmissionTime	InpatientDischargeTime
2	24689		18/06/1935	WHITE - BRITISH	123 BUSY LANE	WK12 123	Sacred Heart	24/09/2021		01/01/2021	123 Random		NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N	NOT APPLICABLE -PATIENT DIED/STILL BIRTH
3	24690		15/09/1944	WHITE - BRITISH	456 Bee Drive	WK12 124	St Judes	25/09/2021		01/01/2021	111 Random		NOT APPLICABLE -PATIENT DIED/STILL BIRTH	N	NOT APPLICABLE -PATIENT DIED/STILL BIRTH
4	24691		18/06/1936	Black	124 BUSY LANE	WK12 125	Mash	26/09/2021		01/01/2021	333 Random		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
5	24692		15/09/1945	Black	457 Bee Drive	WK12 126	Chicago Hope	27/09/2021		01/01/2021	444 Random		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
6	24693		18/06/1937	Black	125 BUSY LANE	WK12 127	St Elmo	28/09/2021		01/01/2021	555 Random		NOT APPLICABLE -PATIENT DIED/STILL BIRTH		NOT APPLICABLE -PATIENT DIED/STILL BIRTH
Sheet1

Reddit2.xlsx
	P
2	N
Sheet1

 

[Alex Blakenburg]

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.