Search code examples
excelpowerquery

Powerquery transform for grouping by State

I have a report that I'm given monthly that I'm hoping to clean up and automate using Powerquery. It is a summary of sales performance, grouped by store location, so that every four or five rows, there is a row that's just displaying city name, like so:

Products: Dallas Apples|3,212 Oranges|1,253 Bananas|4,394 Seattle Apples|598 Oranges|3,409 Bananas|3,120 Grapes|2,341

How would I be able to add a column, and assign the city value to that column for each of those respective rows?

I have tried transposing operations in powerquery, but have not yet figured out if that's possible, or the best method.

Solution

  • Suppose you start from this:

    enter image description here

    Then you can add a custom column:

    enter image description here

    Then use fill-down on the new column:

    enter image description here

    Which will give you this:

    enter image description here

    Finally you can filter out the rows with null sales:

    enter image description here

    To get:

    enter image description here