cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

How to merge or combine 2 tables with different structure

Hi all

 

How can I merge 2 tables that have different structure?

 

I would like to merge to a table called "Lines" the months from table "Months & units"

 

In this case the table lines should go from this:

 

dsfdsfs.JPG

 

To this:

 

rdeires.JPG

 

As seen, each of the 3 products or row, were assigned all the available months. So, column F "month" highlighted in light blue is the result desired.

 

The months as mentioned come from table "Months & units" but I also added on power bi a month table with its unique values called "month catalog"

 

How can I get the months merged to my table "lines"?

 

pbix and excel are attached:

 

https://1drv.ms/u/s!ApgeWwGTKtFdhni9c6kuDioLhq0H?e=SvaW8g

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

Hi,

See the Lines table of this file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

You can do a table merge on more than one column. Select all the common columns from both tables during the dialog.

 

Another way is to use a custom combiner function for Table.AddColumn .

Highlighted
Post Prodigy
Post Prodigy

Re: How to merge or combine 2 tables with different structure

Hi @lbendlin 

 

And how to I merge both? From table "Month Catalog" or "Month & Units" to table "lines" by using Power Query.

 

Thanks.

Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

For example:

 

let
    Source = Table.NestedJoin(Lines, {"Country", "Plant", "Line"}, #"Months & Units", {"Country", "Plant", "Line"}, "Months & Units", JoinKind.Inner)
in
    Source
Highlighted
Post Prodigy
Post Prodigy

Re: How to merge or combine 2 tables with different structure

Hi @lbendlin 

 

I want to my table lines, opened the editor and did this:

 

asdasdasd.JPG

But got an error, reading your code, where is it bringing the months to the table lines?

 

Thanks.

Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

You need to put my code in a new, blank query

Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

Hi,

See the Lines table of this file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: How to merge or combine 2 tables with different structure

Hi @Ashish_Mathur 

 

I see this

 

asdada.JPG

 

Regards.

Highlighted
Super User V
Super User V

Re: How to merge or combine 2 tables with different structure

Hi,

Save the Excel file on your desktop.  Double click on Source and link to the Excel file on your desktop.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors