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

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

 

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