Power Query Workout 01 - Append Data with Different Column Headers

Matthias · March 26, 2023, 10:44pm

Difficulty Rating:

Introduction:

If you build complex reports combining data from various sources, changing header names can be a challenge. This week’s challenge simulates the type of scenario you commonly might run into regarding inconsistency in column header names, caused by situations such as:

The column names are changed by IT at the source.
Colleagues from other countries use a different language in their files.
The year end version of the download uses different abreviations.
There was a reorganisation and header names are changed to reflect the new organisational naming convention.
…

Your task:
You need to append the data which you received from Japan to your Data query.

This is a beginners workout => Post screenshots of your UI driven solution.

(NOTE: For more advanced users, attempt to minimize the number of manual changes using relevant M code functions)

1 Append with different Header Names.xlsx (27.8 KB)

This workout will be released on Monday March 27, 2023, and the author’s solution will be posted on Sunday April 2, 2023.

KimC · March 28, 2023, 3:38am

Hi Matthias,
Thanks for the workout. Here is my solution:

let
Source = Folder.Files(“C:\Users\cookki\OneDrive - Hewlett Packard Enterprise\HPE\Excel\Excel\Power Query\Workouts Ch1”),
#“Filtered Hidden Files1” = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#“Invoke Custom Function1” = Table.AddColumn(#“Filtered Hidden Files1”, “Transform File”, each #“Transform File”([Content])),
RenamedColumns1 = Table.RenameColumns(#“Invoke Custom Function1”, {“Name”, “Source.Name”}),
RemovedOtherColumns1 = Table.SelectColumns(RenamedColumns1, {“Source.Name”, “Transform File”}),
AddedCustom = Table.AddColumn(RemovedOtherColumns1, “Custom”, each Table.DemoteHeaders([Transform File])),
RemovedColumns = Table.RemoveColumns(AddedCustom,{“Source.Name”, “Transform File”}),
ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, “Custom”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”, “Column23”, “Column24”, “Column25”, “Column26”, “Column27”, “Column28”, “Column29”, “Column30”, “Column31”, “Column32”, “Column33”, “Column34”, “Column35”, “Column36”, “Column37”, “Column38”, “Column39”, “Column40”, “Column41”, “Column42”, “Column43”, “Column44”, “Column45”, “Column46”, “Column47”, “Column48”, “Column49”, “Column50”, “Column51”, “Column52”, “Column53”, “Column54”, “Column55”}, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”, “Column23”, “Column24”, “Column25”, “Column26”, “Column27”, “Column28”, “Column29”, “Column30”, “Column31”, “Column32”, “Column33”, “Column34”, “Column35”, “Column36”, “Column37”, “Column38”, “Column39”, “Column40”, “Column41”, “Column42”, “Column43”, “Column44”, “Column45”, “Column46”, “Column47”, “Column48”, “Column49”, “Column50”, “Column51”, “Column52”, “Column53”, “Column54”, “Column55”}),
PromotedHeaders = Table.PromoteHeaders(ExpandedCustom, [PromoteAllScalars=true]),
Remove2ndTblHeaders = Table.SelectRows(PromotedHeaders, each ([Location] <> “Branch”)),
ChType = Table.TransformColumnTypes(Remove2ndTblHeaders,{{“Location”, type text}, {“Customer”, type text}, {“Customer Nr.”, type text}, {“cw01”, Int64.Type}, {“cw02”, Int64.Type}, {“cw03”, Int64.Type}, {“cw04”, Int64.Type}, {“cw05”, Int64.Type}, {“cw06”, Int64.Type}, {“cw07”, Int64.Type}, {“cw08”, Int64.Type}, {“cw09”, Int64.Type}, {“cw10”, Int64.Type}, {“cw11”, Int64.Type}, {“cw12”, Int64.Type}, {“cw13”, Int64.Type}, {“cw14”, Int64.Type}, {“cw15”, Int64.Type}, {“cw16”, Int64.Type}, {“cw17”, Int64.Type}, {“cw18”, Int64.Type}, {“cw19”, Int64.Type}, {“cw20”, Int64.Type}, {“cw21”, Int64.Type}, {“cw22”, Int64.Type}, {“cw23”, Int64.Type}, {“cw24”, Int64.Type}, {“cw25”, Int64.Type}, {“cw26”, Int64.Type}, {“cw27”, Int64.Type}, {“cw28”, Int64.Type}, {“cw29”, Int64.Type}, {“cw30”, Int64.Type}, {“cw31”, Int64.Type}, {“cw32”, Int64.Type}, {“cw33”, Int64.Type}, {“cw34”, Int64.Type}, {“cw35”, Int64.Type}, {“cw36”, Int64.Type}, {“cw37”, Int64.Type}, {“cw38”, Int64.Type}, {“cw39”, Int64.Type}, {“cw40”, Int64.Type}, {“cw41”, Int64.Type}, {“cw42”, Int64.Type}, {“cw43”, Int64.Type}, {“cw44”, Int64.Type}, {“cw45”, Int64.Type}, {“cw46”, Int64.Type}, {“cw47”, Int64.Type}, {“cw48”, Int64.Type}, {“cw49”, Int64.Type}, {“cw50”, Int64.Type}, {“cw51”, Int64.Type}, {“cw52”, Int64.Type}})
in
ChType

Anurag · March 28, 2023, 8:03am

Hi @Matthias ,

Here my sol for the work out:

let
    Source = Excel.Workbook(File.Contents("E:\WorkOut\PowerQuery\1 Append with different Header Names.xlsx"), null, true),
    JP_Table = Source{[Item="JP",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(JP_Table,{{"Branch", type text}, {"Cust.", type text}, {"Customer #", type text}, {"情報WK01", Int64.Type}, {"情報WK02", Int64.Type}, {"情報WK03", Int64.Type}, {"情報WK04", Int64.Type}, {"情報WK05", Int64.Type}, {"情報WK06", Int64.Type}, {"情報WK07", Int64.Type}, {"情報WK08", Int64.Type}, {"情報WK09", Int64.Type}, {"情報WK10", Int64.Type}, {"情報WK11", Int64.Type}, {"情報WK12", Int64.Type}, {"情報WK13", Int64.Type}, {"情報WK14", Int64.Type}, {"情報WK15", Int64.Type}, {"情報WK16", Int64.Type}, {"情報WK17", Int64.Type}, {"情報WK18", Int64.Type}, {"情報WK19", Int64.Type}, {"情報WK20", Int64.Type}, {"情報WK21", Int64.Type}, {"情報WK22", Int64.Type}, {"情報WK23", Int64.Type}, {"情報WK24", Int64.Type}, {"情報WK25", Int64.Type}, {"情報WK26", Int64.Type}, {"情報WK27", Int64.Type}, {"情報WK28", Int64.Type}, {"情報WK29", Int64.Type}, {"情報WK30", Int64.Type}, {"情報WK31", Int64.Type}, {"情報WK32", Int64.Type}, {"情報WK33", Int64.Type}, {"情報WK34", Int64.Type}, {"情報WK35", Int64.Type}, {"情報WK36", Int64.Type}, {"情報WK37", Int64.Type}, {"情報WK38", Int64.Type}, {"情報WK39", Int64.Type}, {"情報WK40", Int64.Type}, {"情報WK41", Int64.Type}, {"情報WK42", Int64.Type}, {"情報WK43", Int64.Type}, {"情報WK44", Int64.Type}, {"情報WK45", Int64.Type}, {"情報WK46", Int64.Type}, {"情報WK47", Int64.Type}, {"情報WK48", Int64.Type}, {"情報WK49", Int64.Type}, {"情報WK50", Int64.Type}, {"情報WK51", Int64.Type}, {"情報WK52", Int64.Type}}),
    #"ColName from Data" = Table.ColumnNames(Data),
    RenameColumn = Table.RenameColumns(   #"Changed Type",List.Zip({Table.ColumnNames( #"Changed Type"),#"ColName from Data"})),
    #"Appended Query" = Table.Combine({ Data,RenameColumn})
in
    #"Appended Query"

PowerQuery1.pbix (126.7 KB)

Sue · March 28, 2023, 7:14pm

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
ColNames = Table.ColumnNames(Source),
JP = Excel.CurrentWorkbook(){[Name=“JP”]}[Content],
ColNamesJP = Table.ColumnNames(JP),
ChangeColNames = Table.RenameColumns(JP, List.Zip({ColNamesJP, ColNames})),
Append = Table.Combine({Source, ChangeColNames})
in
Append

BrianJ · March 28, 2023, 8:39pm

@Matthias ,

Thanks - great practical scenario to kick off with.

Here’s my solution:

Click for Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    JPTable = Excel.CurrentWorkbook(){[Name="JP"]}[Content],
    JPRenameListZip = Table.RenameColumns( JPTable, List.Zip( {Table.ColumnNames(JPTable), Table.ColumnNames(Source)}), null),
    Append = Table.Combine( {Source, JPRenameListZip})
in
    Append

valeriabreveglieri · March 29, 2023, 3:28pm

Hello, here is my take on it:

Thanks a lot for the challenge!

Kind regards
Valeria

let
Source = Excel.Workbook(File.Contents(“MyPath\1 Append with different Header Names.xlsx”), null, true),
#“Filtered Rows” = Table.SelectRows(Source, each ([Kind] = “Table”)),
Data_Table = #“Filtered Rows”{[Item=“Data”,Kind=“Table”]}[Data],
JP_Table = #“Filtered Rows”{[Item=“JP”,Kind=“Table”]}[Data],
Custom2 = List.Zip({Table.ColumnNames(JP_Table),Table.ColumnNames(Data_Table)}),
RenamedColumnsJP=Table.RenameColumns( JP_Table, Custom2,MissingField.Ignore),
FinalTable=Table.Combine({Data_Table,RenamedColumnsJP})
in
FinalTable

Luan · March 30, 2023, 12:42am

Solução

let
Fonte = Table.FromColumns(List.Transform(List.Zip({Table.ToColumns(Data),Table.ToColumns(JP)}),List.Combine),Table.ColumnNames(Data))
in
Fonte

graeme.mccumiskey · March 30, 2023, 12:54am

@Matthias,

Here is my submission.

let
Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content], // Get NY Data
JPData = Excel.CurrentWorkbook(){[Name = “JP”]}[Content], // Get JPN Data
JPRename = Table.RenameColumns( // Renames Col Headers in JPN Data
JPData,
List.Zip({Table.ColumnNames(JPData), Table.ColumnNames(Source)}), // Generate List of Lists to replace JPN Col Headers with NY Col Headers
MissingField.UseNull // If Col lengths different add null
),
MergeData = Table.Combine({Source, JPRename}) // Merge NY & JPN Data together
in
MergeData

otemitopealice · March 30, 2023, 9:35am

Here is my solution,
I updated the column names with this code and simply used the append queries button in the GUI to append both tables

let
Source = Excel.Workbook(File.Contents(“\Mac\Home\Downloads\1 Append with different Header Names.xlsx”), null, true),
JP_Table = Source{[Item=“JP”,Kind=“Table”]}[Data],

#“Changed Type” = Table.TransformColumnTypes(JP_Table,{{“Branch”, type text}, {“Cust.”, type text}, {“Customer #”, type text}, {“情報WK01”, Int64.Type}, {“情報WK02”, Int64.Type}, {“情報WK03”, Int64.Type}, {“情報WK04”, Int64.Type}, {“情報WK05”, Int64.Type}, {“情報WK06”, Int64.Type}, {“情報WK07”, Int64.Type}, {“情報WK08”, Int64.Type}, {“情報WK09”, Int64.Type}, {“情報WK10”, Int64.Type}, {“情報WK11”, Int64.Type}, {“情報WK12”, Int64.Type}, {“情報WK13”, Int64.Type}, {“情報WK14”, Int64.Type}, {“情報WK15”, Int64.Type}, {“情報WK16”, Int64.Type}, {“情報WK17”, Int64.Type}, {“情報WK18”, Int64.Type}, {“情報WK19”, Int64.Type}, {“情報WK20”, Int64.Type}, {“情報WK21”, Int64.Type}, {“情報WK22”, Int64.Type}, {“情報WK23”, Int64.Type}, {“情報WK24”, Int64.Type}, {“情報WK25”, Int64.Type}, {“情報WK26”, Int64.Type}, {“情報WK27”, Int64.Type}, {“情報WK28”, Int64.Type}, {“情報WK29”, Int64.Type}, {“情報WK30”, Int64.Type}, {“情報WK31”, Int64.Type}, {“情報WK32”, Int64.Type}, {“情報WK33”, Int64.Type}, {“情報WK34”, Int64.Type}, {“情報WK35”, Int64.Type}, {“情報WK36”, Int64.Type}, {“情報WK37”, Int64.Type}, {“情報WK38”, Int64.Type}, {“情報WK39”, Int64.Type}, {“情報WK40”, Int64.Type}, {“情報WK41”, Int64.Type}, {“情報WK42”, Int64.Type}, {“情報WK43”, Int64.Type}, {“情報WK44”, Int64.Type}, {“情報WK45”, Int64.Type}, {“情報WK46”, Int64.Type}, {“情報WK47”, Int64.Type}, {“情報WK48”, Int64.Type}, {“情報WK49”, Int64.Type}, {“情報WK50”, Int64.Type}, {“情報WK51”, Int64.Type}, {“情報WK52”, Int64.Type}}),

#“Replace Column Name” = Table.TransformColumnNames(#“Changed Type”, (columnName as text) as text => Text.Replace(columnName, “情報WK”, “cw”)),

#“Rename Columns” = Table.RenameColumns(#“Replace Column Name”,{{“Cust.”,“Customer”},{“Branch”,“Location”},{“Customer #”,“Customer Nr.”}})

in
#“Rename Columns”

HermioneGranger1176 · March 30, 2023, 11:59am

My Report Table Name is OEM. Now, you can add as many tables you want. Only thing is keep the columns sequence consistent.

let
	Source    = Excel.CurrentWorkbook(),
	Filter    = Table.SelectRows ( Source, each [Name] <> "OEM" ),
	Sort      = Table.Sort ( Filter, { { "Name", Order.Ascending } } ),
	ColNames  = Table.ColumnNames ( Source{[ Name = "Data" ]}[Content] ),
	Transform = Table.TransformColumns (
		Sort,
		{
			"Content",
			each Table.RenameColumns ( _, List.Zip ( { Table.ColumnNames ( _ ), ColNames } ) )
		}
	),
	Return    = Table.Combine ( Transform[Content] )
in
	Return

1 Append with different Header Names.xlsx (38.0 KB)

Pavel.Adam · March 30, 2023, 7:38pm

I assume that

tables have columns in identical sequence
tables have identical number of columns
definition of correct data types of the columns is not required (i.e. output of the query is any type that is in accord with input query “Data” and “JP”)
input data are defined using the query “Data” and “JP”

Screenshot:

PQ code:
Table.Combine(
{ Data,
Table.RenameColumns(JP, List.Zip({Table.ColumnNames(JP), Table.ColumnNames(Data)}))
}
)`

fidel57 · March 30, 2023, 10:43pm

let
Source = Excel.CurrentWorkbook(),
#“Filtered Rows” = Table.SelectRows(Source, each ([Name] <> “OEM”)),
#“Sorted Rows” = Table.Sort(#“Filtered Rows”,{{“Name”, Order.Ascending}}),
#“Added Custom” = Table.AddColumn(#“Sorted Rows”, “Custom”, each Table.DemoteHeaders([#“Content”])),
#“Removed Columns” = Table.RemoveColumns(#“Added Custom”,{“Content”}),
#“Expanded Custom” = Table.ExpandTableColumn(#“Removed Columns”, “Custom”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19”, “Column20”, “Column21”, “Column22”, “Column23”, “Column24”, “Column25”, “Column26”, “Column27”, “Column28”, “Column29”, “Column30”, “Column31”, “Column32”, “Column33”, “Column34”, “Column35”, “Column36”, “Column37”, “Column38”, “Column39”, “Column40”, “Column41”, “Column42”, “Column43”, “Column44”, “Column45”, “Column46”, “Column47”, “Column48”, “Column49”, “Column50”, “Column51”, “Column52”, “Column53”, “Column54”, “Column55”}, {“Custom.Column1”, “Custom.Column2”, “Custom.Column3”, “Custom.Column4”, “Custom.Column5”, “Custom.Column6”, “Custom.Column7”, “Custom.Column8”, “Custom.Column9”, “Custom.Column10”, “Custom.Column11”, “Custom.Column12”, “Custom.Column13”, “Custom.Column14”, “Custom.Column15”, “Custom.Column16”, “Custom.Column17”, “Custom.Column18”, “Custom.Column19”, “Custom.Column20”, “Custom.Column21”, “Custom.Column22”, “Custom.Column23”, “Custom.Column24”, “Custom.Column25”, “Custom.Column26”, “Custom.Column27”, “Custom.Column28”, “Custom.Column29”, “Custom.Column30”, “Custom.Column31”, “Custom.Column32”, “Custom.Column33”, “Custom.Column34”, “Custom.Column35”, “Custom.Column36”, “Custom.Column37”, “Custom.Column38”, “Custom.Column39”, “Custom.Column40”, “Custom.Column41”, “Custom.Column42”, “Custom.Column43”, “Custom.Column44”, “Custom.Column45”, “Custom.Column46”, “Custom.Column47”, “Custom.Column48”, “Custom.Column49”, “Custom.Column50”, “Custom.Column51”, “Custom.Column52”, “Custom.Column53”, “Custom.Column54”, “Custom.Column55”}),
#“Filtered Rows1” = Table.SelectRows(#“Expanded Custom”, each ([Name] <> “Query1”)),
#“Promoted Headers” = Table.PromoteHeaders(#“Filtered Rows1”, [PromoteAllScalars=true]),
#“Filtered Rows2” = Table.SelectRows(#“Promoted Headers”, each ([Location] <> “Branch”)),
#“Removed Columns1” = Table.RemoveColumns(#“Filtered Rows2”,{“Data”})
in
#“Removed Columns1”

borydobon · March 31, 2023, 3:07pm

Query Name OEMres

let
Source = Excel.CurrentWorkbook(),
Filtered = Table.SelectRows(Table.Sort(Source,{{“Name”,0}}), each not Text.StartsWith([Name],“OE”))[Content],
Combine = Table.PromoteHeaders(Table.Combine(List.Transform(Filtered,each Table.Skip(Table.DemoteHeaders(_), if [Location]? <> null then 0 else 1))))
in
Combine

peps984 · April 1, 2023, 8:10pm

Hi, nice challenge. Since it’s a beginner challenge, I’ve used only UI, no custom function or advanced editor.

This is my solution for the editing of the JP table:

Then I’ve just combined the two tables

Matthias · April 2, 2023, 8:44pm

Hello,
thanks to everyone who participated! In Power Query there is not only one correct solution, but there are always several solutions. Everyone who solved the workout is a winner!

Here are the two solutions I promised:

1 UI Driven
Prepare JP query:
a. Demote Headers
Transform tab > Use First Row as Headers > Use Headers as First Row

b. Remove (Skip) First Row
Home tab > Remove Rows > Remove Top Rows > insert 1 into Remove Top Rows menu

Append JP query to Data Query:
a. Demote Headers
Transform tab > Use First Row as Headers > Use Headers as First Row

b. Append JP Query
Home tab > Append Queries (no need for the dropdown) > Select JP query in the Append menu

c. Promote Headers
Transform tab > Use First Row as Headers (just click on the big button)

=> So after 2 (JP) plus 3 (Data) steps we are done. 100% UI Driven:

2 Append JP and Adjust in the Formula Bar
a. Home tab > Append Queries (no need for the dropdown) > Select JP query in the Append menu
That will give you a basic syntax, which you can adapt in the formular bar:
= Table.Combine({Source, JP})
= Table.Combine({ Table.DemoteHeaders(Source), Table.Skip(Table.DemoteHeaders(JP), 1)})
b. Promote Headers
Transform tab > Use First Row as Headers (just click on the big button)

For me this 2 step solution strikes a good balance between UI support and manual effort, so I hope it is still beginner friendly. But have a look also at other solutions, following completely different approaches, some of them are very compact!

Big thanks to everyone who has blured or hidden the details! FYI: I have taken this now out so that it’s easier to compare solutions. => Hope you learn something!

BrianJ · April 2, 2023, 8:59pm

Matthias,

Tremendous job on this workout, both in the problem formulation and in the explanation. I love your innovative use of the UI to accomplish tasks - my solution was full-on M custom coding because I didn’t see a way to do this via the UI, so I actually learned a lot from your creative UI-driven solution.

Thanks! Can’t wait for your workout #2…

Brian

donnclark · April 11, 2023, 11:28pm

Donn Clark

Here’s my solution … working to catch up on the workouts.

Thanks
April 11, 2023

AlejandroSimon · April 24, 2023, 1:18am

Adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
RowsNy = Table.ToRows(Source),
Sol = Table.FromRows(RowsNy&Table.ToRows(Excel.CurrentWorkbook(){[Name=“JP”]}[Content]), Table.ColumnNames(Source))
in
Sol

willow.lang · April 26, 2023, 10:05am

Here is my solution
let
Source = Excel.CurrentWorkbook(),

SelectTables =
Table.SelectRows(Source, each List.Contains({“JP”,“Data”,“OEM”},[Name])),

ColumnHeaders =
Table.ColumnNames(SelectTables{[Name=“Data”]}[Content]),

TransformTables =
Table.TransformColumns(
SelectTables,{{“Content”,each Table.Skip(Table.DemoteHeaders(_),1)}}),

ColumnHeadersToChange =
Table.ColumnNames(TransformTables{[Name=“Data”]}[Content]),

ExpandTableContent =
Table.ExpandTableColumn(
TransformTables,
“Content”,
ColumnHeadersToChange,
ColumnHeaders)
in
ExpandTableContent

luisss5678 · May 31, 2023, 1:55pm

Great challenge! I’ve used “Use First Row as Headers” what feels like a million times before but I had never even noticed the drop-down arrow for “Use Headers as First Row”. I first started going down the path of writing M code to rename all the wk columns but I thought there must be a better way so I did take a peek at the solution. What a clever way of using the empty “column” names to make sure both datasets could be appended correctly.

Thanks again for the challenge! Can’t wait to dig into the rest.