Forum Discussion
Combining two table with different columns name
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.
- This query contains a list of lists of old/new pairs, for example {Column1_Old, Column1_New}
- *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.
- Create a new blank query: = tblReplacement[Sipee]
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.
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.
- This query contains a list of lists of old/new pairs, for example {Column1_Old, Column1_New}
- *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.
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"
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.