Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stefanvdz
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
Anonymous
Not applicable

 

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

8 REPLIES 8
padinator
Helper I
Helper I

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

Anonymous
Not applicable

 

 

 

 

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.

Anonymous
Not applicable

 

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

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors