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

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.

View attachment 43472

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"
Click to expand...

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.

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.

View attachment 43472

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"
Click to expand...

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?
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
Click to expand...
It still says the same "
Expression.Error: The column 'BEFORE' of the table wasn't found.
Details:
BEFORE"
 
Upvote 0
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
 
Upvote 0
Sorry, I feel stupid but where am I going wrong?

<< unavailable video removed >>

I think I set it up as it should be but I am not sure how to use the above queries so it loads.
 
Last edited by a moderator:
Upvote 0
Alex Blakenburg said:
Are you able to send me a copy of your file using Dropbox or onedrive or SharePoint ?
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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.
 
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,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

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