Transpose Duplicate Rows into Repeated Columns during Query Merge

Posts: 2
Joined: Tue Apr 04, 2017 8:04 pm

Transpose Duplicate Rows into Repeated Columns during Query Merge

Postby RayneMan » Tue Apr 04, 2017 8:13 pm

Hi all,

I'm very hopeful you guys might be able to help me with an issue I'm facing. In case it's relevant I'm using Excel 2013 with the latest PowerQuery update (2.42.4611.421).

Basically I have two source tables, as shown in the screenshot below:
  1. DateTable: simply an incremental list of dates;
  2. Events: a table of events listed by date, name, and address.
I'd like to merge the two such that the end result looks like a calendar, ie. a unique list of dates, one per row, with each event alongside and as many columns added to ensure all events are displayed ('Desired' table in screenshot).

My most basic attempt ends up with extra rows where multiple events happen on the same date ('Actual' table in screenshot). A few more complicated efforts result in things like simply removing the duplicate dates (and therefore losing the additional events), or manually adding extra columns and transposing the extra same-day events into those columns, but it isn't really dynamic - I'd like for it account for 20 events falling on the same day just as easily as 2.

Is this actually possible? Any pointers on how? Or perhaps I'm overlooking some simple rationale for why this shouldn't be done or some alternate path taken?


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests