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]

THa

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.

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

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.

One file will be

NHS Number DOB Ethnicity PatientAddress1 PatientAddress2 PatientAddress3 PatientAddress4 PatientPostcode Hospital DateAdmitted DateTestedPositive DateDischarged (if applicable) DischargeDestination Patient 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]

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]

What should I replace Parameter 1 with?

 

[Alex Blakenburg]

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]

Here is the mapping table I used

BEFORE	AFTER
NHS Number	NHS Number
DOB	DOB
Ethnicity	Ethnicity (from Hospital)
PatientAddress1	PatientAddress1
PatientPostcode	Postcode
Hospital	Hospital
DateAdmitted	DateAdmitted
DateTestedPositive	Date tested Positive
DateDischarged (if applicable)	Date discharged (if applicable)
DischargeDestination	Discharge Destination
NHSNumber	NHS Number
DateOfBirth	DOB
EthnicCategory	Ethnicity (from Hospital)
PatientAddress	PatientAddress1
Postcode	Postcode
Hospital	Hospital

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]

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

DateDischarged	Date discharged (if applicable)	Reddit2
AddressDischargedTo	Discharge Destination	Reddit2

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]

= 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

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

DateDischarged	Date discharged (if applicable)	Reddit2
AddressDischargedTo	Discharge Destination	Reddit2

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)

 

[Alex Blakenburg]

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]

"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]

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)