Flatten a table where a field can have multiple values

i am doing a web request via api which returns a table similar to this:

Was trying pivots but i cannot get this right.

Thanks,

Cris

A few things going on here. Metrics need to be aggregated, and tags need to be pivoted. So it would make sense to process them separately. See the example below.

aggregate-pivot.morph (4.6 KB)
Book1.xlsx (8.7 KB)

Gotchas:

  • The “Merge” must be in the left join mode
  • “Running total” must be grouped by account ID

Thanks Dmitry. That worked great!!!

Luckily my metrics that i get via api are pre-aggregated so when separate tags, flatten, unpivot/pivot it all (my base and the tags only tables) so i have one record in my tags with all the individual tag1, tag2, etc. then merge back to my original table by the unique client id everything is good. The separation of the tags was the key. Appreciate your help!

You’re welcome :slight_smile: