cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Need hep on joining two tables and mapping the columns

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:

 

tabel map.png

 

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

1 ACCEPTED 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)

 

 

 

 

View solution in original post

7 REPLIES 7
Resident Rockstar
Resident Rockstar

 

 

 

 

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)

 

 

 

 

View solution in original post

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 !

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors