Combining two table with different columns name

[DanFromMontreal]

Hello all,

I would like to combine 2 different tables having different number of columns (59, 51) and for the ones that have the same type of info, the header is NOT written the same way.

I went through the similar subject and found one that could resolve my problem.

Source = Table.Combine({T1, Table.RenameColumns(T2,{{"Manager","Account Manager"},{"Location Name","Location"}})})

Having more then 48 columns with different name, this could get combersome fast.

I though of a creating a table on a separed worksheet having 2 columns ("Old name" and "New name") for all mismatch then LOAD it as a connection only.

After that, I would do, for each header column name of table 2, a "Search and Replace" from this replacement table.  If not found, just keep the one indicated in T2.

Is it feasable or I'm just dreaming.

Thank you for all the help.

[supremo_pdx · Accepted solution]

I'll walk your through it step-by-step to see what's happening at each step.

To demonstrate how we are preping the replacement values for use in List.ReplacementMatchingItems:

• Create a new blank query: = tblReplacement[Sipee]
  • Rename the query "Sipee"
  • Do the same for tblReplacement[Siebel]
  • What you you should have are two queries containing a list of values for each respective column in your replacement table
• Create another blank query: = List.Zip( { Sipee, Siebel} )
  • This query contains a list of lists of old/new pairs, for example {Column1_Old, Column1_New}
    • To check, click in the gray area next any "List" row (clicking of the "List word will expand that list) to see which values are in that list.
• *List.Buffer() simply puts this list of lists in memory, so that the matching step is faster
• So ultimately, we are creating a list of each row-value pair for Sipee and Siebel

Now for what List.ReplaceMatchingItems is doing:

• Table.TransformColumnNames(#"En-tetes promus", each...
  • Translation: "For each column name in the table", represented further by {_}
• List.ReplaceMatchingItems(list as list, replacements as list, optional equationCriteria as any) as list
  • Lists as list: for this parameter we use is {_}, which represents each column name
  • Replacements as list: using our replacement table, essentially what this does is if any of the column names matches any the first (old/sipee) items in the old/new pair, replace it with the second (new/siebel) item for that match
  • equationCriteria is Comparer.OrdinalIgnoreCase, which just says don't mind mismatched cases
• Finally, we wrap the list of replaced values in Text.Combine which converts each list item back to a text value.

As for why it isn't working, it's hard to say by looking at the error you received and without having your file. But try those test queries above to get a feel for it and see if it makes more sense. 

[DanFromMontreal]

@Anonymous , Thank for your reply but I cannot read your PBIX example as I do not have PowerBI installed.  Using PowerEditor from Excel

Would it be possible for you to copy/paste the code here so I can understand your solution

Grazie

[supremo_pdx]

If you have a replacement table with Old/New columns and both it and the two tables of interest are loaded to to Power Query, the following should work:

• As an example, let's say your query holding the replacement table is called "Replacement" and it has two columns, "Old" and "New" -
• Create a new step in whichever table that you want to change the columns names and enter the following code:
  • #"Replace Cols Names" = Table.TransformColumnNames(#"Previous Step", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { Old,New } )), Comparer.OrdinalIgnoreCase)))
• Then you should be able to use Table.Combine to create a single 59 column table, with rows from the 51 table simply being null in the additional 8 columns.

[DanFromMontreal]

@supremo_pdx , I get the error "Old" (in my case Sipee) is not recognize.  

Where do I make reference of the replacement table name (Replacement) ???

All these functions are new to me

PS:  my Power Editor is in French

let
Source = Excel.CurrentWorkbook(){[Name="tblProjetSipee"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"NUMÉRO_DU_SYSTÈME_SOURCE", Int64.Type}, {"PARTENAIRE", Int64.Type}, {"RESPONSABLE_TECHNIQUE", type text}}),
//#"Replace Cols Names" = Table.TransformColumnNames(#"Previous Step", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { Old,New } )), Comparer.OrdinalIgnoreCase)))
#"Replace Cols Names" = Table.TransformColumnNames(#"Type modifié", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { Sipee,Siebel } )), Comparer.OrdinalIgnoreCase)))
in
//#"Type modifié"
#"Replace Cols Names"

//Syntax: List.ReplaceMatchingItems(list as list, replacements as list, optional equationCriteria as any) as list

[supremo_pdx]

Ah that was my mistake, I had already assigned the column lists to a variable in my editor. In general, you can access the list of values in a table column like so: Table[Column] = list of values in that column. Therefore:

let
Source = Excel.CurrentWorkbook(){[Name="tblProjetSipee"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"NUMÉRO_DU_SYSTÈME_SOURCE", Int64.Type}, {"PARTENAIRE", Int64.Type}, {"RESPONSABLE_TECHNIQUE", type text}}),
#"Replace Cols Names" = Table.TransformColumnNames(#"Type modifié", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { Replacement[Sipee],Replacement[Siebel] } )), Comparer.OrdinalIgnoreCase)))
in
#"Replace Cols Names"

[DanFromMontreal]

Nope, still get a huge error message.

I tried to understand each step of the formula.

I do not get what the underscore does in ...List.ReplaceMatchingItems({_}...  

Isn't the {_}  suppose to be my Header columns?

[supremo_pdx · Accepted solution]

I'll walk your through it step-by-step to see what's happening at each step.

To demonstrate how we are preping the replacement values for use in List.ReplacementMatchingItems:

• Create a new blank query: = tblReplacement[Sipee]
  • Rename the query "Sipee"
  • Do the same for tblReplacement[Siebel]
  • What you you should have are two queries containing a list of values for each respective column in your replacement table
• Create another blank query: = List.Zip( { Sipee, Siebel} )
  • This query contains a list of lists of old/new pairs, for example {Column1_Old, Column1_New}
    • To check, click in the gray area next any "List" row (clicking of the "List word will expand that list) to see which values are in that list.
• *List.Buffer() simply puts this list of lists in memory, so that the matching step is faster
• So ultimately, we are creating a list of each row-value pair for Sipee and Siebel

Now for what List.ReplaceMatchingItems is doing:

• Table.TransformColumnNames(#"En-tetes promus", each...
  • Translation: "For each column name in the table", represented further by {_}
• List.ReplaceMatchingItems(list as list, replacements as list, optional equationCriteria as any) as list
  • Lists as list: for this parameter we use is {_}, which represents each column name
  • Replacements as list: using our replacement table, essentially what this does is if any of the column names matches any the first (old/sipee) items in the old/new pair, replace it with the second (new/siebel) item for that match
  • equationCriteria is Comparer.OrdinalIgnoreCase, which just says don't mind mismatched cases
• Finally, we wrap the list of replaced values in Text.Combine which converts each list item back to a text value.

As for why it isn't working, it's hard to say by looking at the error you received and without having your file. But try those test queries above to get a feel for it and see if it makes more sense. 

[DanFromMontreal]

@supremo_pdx , I've tried this weekend expending this knowledge to replacing values for a particular column using the same tblBeforeAfter table but could not make it work.

See my code below.

The code works up until the #"Replace Cols Names".

The #"Replace Field Value in column" fail.

What Am I doing wrong.

Thank you for your generous support.

 let
Source = Csv.Document(File.Contents("\\rpisln800-sf.bur.hydro.qc.ca\ISA_EE\HQ Affaires\0 Nettoyage\Fichiers de nettoyage\BeforeAfter Replacement file.csv"),[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replace Cols Names" = Table.TransformColumnNames(#"En-têtes promus", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { tblBeforeAfter[Before],tblBeforeAfter[After] } )), Comparer.OrdinalIgnoreCase))),
//syntax: Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as nullable number) as table
#"Replace Field Value in column" = Table.TransformColumns(#"Replace Cols Names", {"Column_B", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfter[Before],tblBeforeAfter[After]}), MissingField.Ignore))))
in
//#"En-têtes promus"
//#"Replace Cols Names"
#"Replace Field Value in column"

[supremo_pdx · Accepted solution]

Nice work! You're very close on the last past, it's just a matter of adjusting the closing brackets:
#"Replace Field Value in column" = Table.TransformColumns(#"Replace Cols Names",{ "Column_B", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfter[Before],tblBeforeAfter[After]})))), type text } ,null,MissingField.Ignore)

I recreated your Excel data and walked through it step-by-step to make sure it worked. You'll notice that I added "type text" before the "}" to skip having to change the type in another step.

Something I would caution is to have one replacement table for column names and another for column values - for example, if tblBeforeAfter has "" (blank) instead of null for [Before] with [After] = Col_H1, it will swap any blank cell with "Col_H1". You can easily do this by duplicating tblBeforeAfter and filtering accordingly. 

[DanFromMontreal]

@supremo_pdx .

Great advice. I'll do that.

Again, your help was much appreciated.

Merci

[DanFromMontreal]

@supremo_pdx , thank you soooooo much for the time you took to explain in details.

I followed your recipe and now I understand a bit more.

However, a was not able to make my my example work but I created a simpler testfile and it worked perfect.

Just need to understand why now that I'm having a fonctionnal example.

Many thanks again

//BeforeAfter Header Replacement using a table
let
Source = Csv.Document(File.Contents("C:\Test\BeforeAfter Header Replacement.csv"),[Delimiter=";", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Col_A", Int64.Type}, {"Col_B", Int64.Type}, {"Col_C", Int64.Type}, {"Col_D", Int64.Type}, {"Col_E", Int64.Type}, {"Col_F", Int64.Type}, {"Col_G", Int64.Type}}),
#"Replace Cols Names" = Table.TransformColumnNames(#"Type modifié", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip( { tblBeforeAfter[Before],tblBeforeAfter[After] } )), Comparer.OrdinalIgnoreCase)))
in
#"Replace Cols Names"

[DanFromMontreal]

@supremo_pdx :  Sorry, see response above