combine tables to a new table

Occasional Visitor


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 

1 Reply

@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.

Screenshot 2021-11-12 at 09.34.52.png

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