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

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

NHS Number

DOB

Ethnicity

PatientAddress1

PatientAddress2

Column1

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

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

WK12 124

ARKHAM

01/01/2021

04/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

N

4

125

18/06/1936

Black

124 BUSY LANE

DEWSBURY

Texas

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

WK12 126

ARKHAM

01/01/2021

04/01/2022

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

6

127

18/06/1937

Black

125 BUSY LANE

DEWSBURY

London

WK12 127

SACRED HEART

01/01/2021

11/01/2023

12/01/2023

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

7

N

Sacred Heart

24/09/2021

8

N

St Judes

25/09/2021

9

Mash

26/09/2021

10

Chicago Hope

27/09/2021

11

St Elmo

28/09/2021

12

Sheet2

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

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

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.

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.

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

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

17

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

18

123

18/06/1935

WHITE - BRITISH

123 BUSY LANE

WK12 123

SACRED HEART

1/01/2021

11/01/2021

12/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

19

124

15/09/1944

WHITE - BRITISH

456 Bee Drive

WK12 124

ARKHAM

1/01/2021

4/01/2021

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

20

125

18/06/1936

Black

124 BUSY LANE

WK12 125

SACRED HEART

1/01/2021

11/01/2022

12/01/2022

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

21

126

15/09/1945

Black

457 Bee Drive

WK12 126

ARKHAM

1/01/2021

4/01/2022

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

22

127

18/06/1937

Black

125 BUSY LANE

WK12 127

SACRED HEART

1/01/2021

11/01/2023

12/01/2023

NOT APPLICABLE -PATIENT DIED/STILL BIRTH

23

24689

18/06/1935

WHITE - BRITISH

123 BUSY LANE

WK12 123

Sacred Heart

24/09/2021

1/01/2021

123 Random

24

24690

15/09/1944

WHITE - BRITISH

456 Bee Drive

WK12 124

St Judes

25/09/2021

1/01/2021

111 Random

25

24691

18/06/1936

Black

124 BUSY LANE

WK12 125

Mash

26/09/2021

1/01/2021

333 Random

26

24692

15/09/1945

Black

457 Bee Drive

WK12 126

Chicago Hope

27/09/2021

1/01/2021

444 Random

27

24693

18/06/1937

Black

125 BUSY LANE

WK12 127

St Elmo

28/09/2021

1/01/2021

555 Random

Mapping

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:

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.xlsx
I J K L M N O P Q R S T U V W X Y Z AA AB
17 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
18 123 18/06/1935 WHITE - BRITISH 123 BUSY LANE WK12 123 SACRED HEART 1/01/2021 11/01/2021 12/01/2021 NOT APPLICABLE -PATIENT DIED/STILL BIRTH NOT APPLICABLE -PATIENT DIED/STILL BIRTH
19 124 15/09/1944 WHITE - BRITISH 456 Bee Drive WK12 124 ARKHAM 1/01/2021 1/01/2021 4/01/2021 NOT APPLICABLE -PATIENT DIED/STILL BIRTH NOT APPLICABLE -PATIENT DIED/STILL BIRTH
20 125 18/06/1936 Black 124 BUSY LANE WK12 125 SACRED HEART 1/01/2021 11/01/2022 12/01/2022 NOT APPLICABLE -PATIENT DIED/STILL BIRTH NOT APPLICABLE -PATIENT DIED/STILL BIRTH
21 126 15/09/1945 Black 457 Bee Drive WK12 126 ARKHAM 1/01/2021 1/01/2021 4/01/2022 NOT APPLICABLE -PATIENT DIED/STILL BIRTH NOT APPLICABLE -PATIENT DIED/STILL BIRTH
22 127 18/06/1937 Black 125 BUSY LANE WK12 127 SACRED HEART 1/01/2021 11/01/2023 12/01/2023 NOT APPLICABLE -PATIENT DIED/STILL BIRTH NOT APPLICABLE -PATIENT DIED/STILL BIRTH
23 24689 18/06/1935 WHITE - BRITISH 123 BUSY LANE WK12 123 Sacred Heart 24/09/2021 1/01/2021 123 Random
24 24690 15/09/1944 WHITE - BRITISH 456 Bee Drive WK12 124 St Judes 25/09/2021 1/01/2021 111 Random
25 24691 18/06/1936 Black 124 BUSY LANE WK12 125 Mash 26/09/2021 1/01/2021 333 Random
26 24692 15/09/1945 Black 457 Bee Drive WK12 126 Chicago Hope 27/09/2021 1/01/2021 444 Random
27 24693 18/06/1937 Black 125 BUSY LANE WK12 127 St Elmo 28/09/2021 1/01/2021 555 Random
Mapping

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

Power Query:

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

Transform Sample File

Power Query:

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

Power Query:

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

Power Query:

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

3

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

4

TemplateRow1

Template

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

Main Incl Mapping Param1 v06.xlsx

A

B

1

BEFORE

AFTER

2

NHS Number

3

DOB

4

Ethnicity

Ethnicity (from Hospital)

5

PatientAddress

PatientAddress1

6

PatientAddress1

7

PatientPostcode

Postcode

8

Hospital

9

DateAdmitted

10

DateTestedPositive

Date tested Positive

11

DateDischarged (if applicable)

Date discharged (if applicable)

12

DischargeDestination

Discharge Destination

13

DateOfBirth

DOB

14

EthnicCategory

Ethnicity (from Hospital)

15

Postcode

16

DateDischarged

Date discharged (if applicable)

17

AddressDischargedTo

Discharge Destination

18

Patient Currently Admitted At Report Generation Time

InpatientAdmissionTime

19

NHSNumber

NHS Number

20

Patient UPRN

21

Patient TertiaryClassification

22

InpatientDischargeTime

23

Trust

24

Combined

25

Admitted_date

26

Tested_date

27

Discharged_date

Mapping

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

Stacker

Board Regular

Stacker

Board Regular

Excel Facts

Alex Blakenburg

MrExcel MVP

Stacker

Board Regular

Alex Blakenburg

MrExcel MVP

Stacker

Board Regular

Stacker

Board Regular

Alex Blakenburg

MrExcel MVP

Stacker

Board Regular

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock