How to merge or combine 2 tables with different structure

[o59393]

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:

To this:

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!

[Ashish_Mathur · Accepted solution]

Hi,

See the Lines table of this file.

Hope this helps.

[o59393]

Hi @Ashish_Mathur 

I see this

Regards.

[Ashish_Mathur]

Hi,

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

[lbendlin]

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 .

[o59393]

Hi @lbendlin 

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

Thanks.

[lbendlin]

For example:

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

[o59393]

Hi @lbendlin 

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

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

Thanks.

[lbendlin]

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