Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    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)

     

     

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

     

     

     

    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's avatar
      Anonymous
      Not applicable
      6 years ago

       

      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)

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

         

  • padinator's avatar
    padinator
    Icon for Helper I rankHelper I
    4 years ago

    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