Merge 2 tables with different rows and duplicate rows on monthly basis

[Hugoleth]

Hello,

I have 2 tables I would like to merge to have only 1 table (final table)

table 3 should be table 1 + for each site and each month the 2 additional rows  (D & E) from table 2.

"Type", "Site" and "Date" from table 1 are linked to some other tables.

"Category" from table 2 also

Once I will have this unique final table, I will modify the formula of column "consumption" to retreive datas for all the merge categories/types (coming from differents other tables). Please can you help

[amitchandak · Accepted solution]

@Hugoleth ,

Duplicate table 1

remove column Type and consumption Table1A

DO a cross Join Table1A, Table 2 Into Table 3 - refer

Power Query Cross Join| Cartesian Product: https://youtu.be/7MvROGObBYk

Then merge Table3 and Table 1

Now you only need to get consumption

[v-yiruan-msft · Accepted solution]

@amitchandak  Thanks for your contribution on this thread.

Hi @Hugoleth ，

I create a sample pbix file(see the attachment), please find the details in it.

As @amitchandak , you can create a calculated table as below to get it by using CROSSJOIN function:

Table =
VAR _tab1 =
    CROSSJOIN (
        VALUES ( 'Table 2'[Category] ),
        VALUES ( 'Table 1'[Sites] ),
        VALUES ( 'Table 1'[Date] )
    )
VAR _tab2 =
    FILTER (
        _tab1,
        NOT ( ISBLANK ( [Sites] ) )
            && NOT ( ISBLANK ( [Date] ) ) && NOT ( ISBLANK ( [Category] ) )
    )
VAR _tab =
    ADDCOLUMNS (
        DISTINCT ( _tab2 ),
        "Consumption",
            CALCULATE (
                MAX ( 'Table 1'[Consumption] ),
                FILTER (
                    'Table 1',
                    'Table 1'[Type] = [Category]
                        && 'Table 1'[Date] = [Date]
                        && 'Table 1'[Sites] = [Sites]
                )
            )
    )
RETURN
    _tab

In addition, you can achieve it in Power Query Editor:

let
    Source = #"Table 2",
    #"Added Custom" = Table.AddColumn(Source, "Site", each List.Distinct(#"Table 1"[Sites])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each List.Distinct(#"Table 1"[Date])),
    #"Expanded Site" = Table.ExpandListColumn(#"Added Custom1", "Site"),
    #"Expanded Date" = Table.ExpandListColumn(#"Expanded Site", "Date"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Date"),
    #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Category", "Site", "Date"}, #"Table 1", {"Type", "Sites", "Date"}, "Table 1", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Consumption"}, {"Consumption"})
in
    #"Expanded Table 1"

Best Regards

[Hugoleth]

It works. Thanks a lot for your support

[Hugoleth]

@amitchandakI forgot to ping you 😉

[amitchandak · Accepted solution]

@Hugoleth ,

Duplicate table 1

remove column Type and consumption Table1A

DO a cross Join Table1A, Table 2 Into Table 3 - refer

Power Query Cross Join| Cartesian Product: https://youtu.be/7MvROGObBYk

Then merge Table3 and Table 1

Now you only need to get consumption

[Hugoleth]

Hello, thanks a lot for your reply. Issue is that when I cross join it's not on Power Query so when I want to merge queries, the cross join is not accessible in Power Query... Is it normal ?