Forum Discussion

DEEPAKSAVANUR's avatar
DEEPAKSAVANUR
Copper Contributor
Nov 11, 2021

combine tables to a new table

Hello,

I was wondering if someone can give me an idea or help me understand How I Can join the tables with slightly different format. please check the attached screenshot tables.png for tables and result.png for the required output.

Thanks in advance 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 12, 2021

    DEEPAKSAVANUR Connect to both tables with PowerQuery. Before you can merge the two, you need to split the first column of Table2 into rows, using " & " (i.e. space & space) as the delimiter.

    Then you can merge the two based on ID and expand the RATING.  Result attached.