Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power Query community,
I have two tables from different sources and with different layouts. I want to merge the contents of each table but I need to map input columns to output columns so similar data from the two tables end up in the same column. Kind of an outer join with column mapping. To illustrate it with an example:
Table A
IDA | OmsA | DatumA | CommentA |
34 | Vierendertig | 13-Feb | test |
25 | vijfentwintig | 24-Apr | een |
6 | Zes | 25-May | twee |
1 | Een | 26-Jun | drie |
Table B
Startdate | Identification | Description | CommentsB |
01-Jan | 101 | Honderd1 | bla |
29-Oct | 201 | Honder2 | blah |
01-Jan | 303 | Honderd3 | balaha |
02-Jan | 304 | Honder34 | blaahaha |
Result
ID | Descr | Date | Comment |
34 | Vierendertig | 13-Feb | test |
25 | vijfentwintig | 24-Apr | een |
6 | Zes | 25-May | twee |
1 | Een | 26-Jun | drie |
101 | Honderd1 | 01-Jan | bla |
201 | Honder2 | 29-Oct | blah |
303 | Honderd3 | 01-Jan | balaha |
304 | Honder34 | 02-Jan | blaahaha |
The mapping is as follows:
I tried various merge and join options but nothing seems to allow me to do the mapping. What function would allow me to do this?
Kind regards,
Stefan
Solved! Go to Solution.
a more "symmetrical" approach
let
colsA=Table.ColumnNames(tabA),
colsB=Table.ColumnNames(tabB),
cols={"ID","Description","Date","Comment"},
mapCols={[A="IDA",B="Identification"],[A="OmsA",B="Description"],[A="DatumA",B="Startdate"],[A="CommentA",B="CommentsB"]},
newCols=List.Accumulate(mapCols,{},(newCol,col)=>newCol&{Table.Column(tabA,col[A])&Table.Column(tabB,col[B])})
in
Table.FromColumns(newCols, cols)
Can you tell me maybe where exactly i can or have to input this recommanded Code example??
Since i want to create a completely new table out of 2 (or 3) existing Tables i would have to somehow create a separate function or how can this be achieved? Sorry i am completely new to this so thats why my maybe "unusual" question! Thanks
let
colsA=Table.ColumnNames(tabA),
colsB=Table.ColumnNames(tabB),
cols={"ID","Description","Date","Comment"},
mapCols=[IDA="Identification",OmsA="Description",DatumA="Startdate",CommentA="CommentsB"],
newCols=List.Accumulate(colsA,{},(s,c)=>s&{Table.Column(tabA,c)&Table.Column(tabB,Record.Field(mapCols,c))})
in
Table.FromColumns(newCols, cols)
check if this help in solving your problem.
a more "symmetrical" approach
let
colsA=Table.ColumnNames(tabA),
colsB=Table.ColumnNames(tabB),
cols={"ID","Description","Date","Comment"},
mapCols={[A="IDA",B="Identification"],[A="OmsA",B="Description"],[A="DatumA",B="Startdate"],[A="CommentA",B="CommentsB"]},
newCols=List.Accumulate(mapCols,{},(newCol,col)=>newCol&{Table.Column(tabA,col[A])&Table.Column(tabB,col[B])})
in
Table.FromColumns(newCols, cols)
That works! Thanks!
I have to dive into this more to understand what is actually happening in the line:
newCols=List.Accumulate(mapCols,{},(newCol,col)=>newCol&{Table.Column(Table1,col[A])&Table.Column(Table13,col[B])})
Quite an expression ....! 🙂
Thanks for your prompt reply.
Kind regards,
Stefan
If you like/need I can explain step by step the expression
Yes, please ! That is very kind of you. I like to understand the expression so I can make changes myself if needed.
you should make the effort to understand my English, as I make the effort to try to write it 🙂
colsA=Table.ColumnNames(tabA), //list of the table A column names
colsB=Table.ColumnNames(tabB), //list of the table B column names
cols={"ID","Description","Date","Comment"}, //list of the new table column names
mapCols={[A="IDA",B="Identification"],[A="OmsA",B="Description"],[A="DatumA",B="Startdate"],[A="CommentA",B="CommentsB"]},
/*
this is a sort of dictionary, in this case is a list of records. Each record containing 2 field called A and B (just to remember where the associated values come from, but they could have been called in any other way). This dictionary represent the mapping between column names lists,
*/
newCols=List.Accumulate( // constructs a list, in this case a list of lists. Each of this lists is(will be) the list values of columns of the new tab.
mapCols, // the dictionary is the list on which to build the new columns of the new table
{}, // starting from empty list
(newCol,col)=>
/* this is the constructor of the list.accumulate wich is a two variables function.
The first one (in this case called newCol) is the status which takes as first value the value of the previous parameter (in this case ‘{}’). The second variable (called col) takes the values from the newCol list at each step.
The result of the function is the new status, i.e., in our case, the new value of newCol parameter.
*/
newCol&{Table.Column(tabA,col[A])&Table.Column(tabB,col[B])})
/* here we have the concatenation of two lists the first one is newCol and the other is the concatenation of two lists/columns.
At the very first step newCol={}, col=mapCols{0}=[A="IDA",B="Identification"] then col[A]=”IDA” and col[B]=”Identification”, so the resulting value is a list which contains the value from tabA[IDA] and tabB[Identification].
The following step newCol is this list and col= mapCols{1}, and so on
*/
in
Table.FromColumns(newCols, cols) //this is just a function which from a list of lists and a list of string get a table.
Wow, that is a powerful expression. Thanks for educating me. By the way: nothing wrong with your English !