Powerquery adding column based on a store location

Question

From a previous question, I'm trying to figure out how to add a new column based on location, when the adjacent cell DOES have a total amount for that location (not null).

Powerquery transform for grouping by State

For location names such as: Dallas, Seattle, New York, DC, and Des Moines. So, the source table would look like:

| Dallas|4,000| |Apples|1,000| |Grapes|2,000| |Bananas|1,000| | Seattle|5,000| |Pears|500| |Apples|2000| |Bananas|2,500| | New York|8,000| |Apples|4,000| |Bananas|2,000| |Grapes|1,000| |Oranges|1,000|

Blockquote

Answer 1

If your city names start with a space, then the answer from @horseyride is simplest.

If they don't start with a space, then as mentioned in my answer to your other question, you can use a list of known cities or known products to separate them.

Suppose you start with this:

Load both tables into Power Query:

Then in your Sales query, you can add a custom column that checks whether a value in the Product column of the Sales table exists in the Location column of the Locations table.

After that, you can use Fill-down as previously (right-click the city column, Fill>Down):

You can then choose to filter out the city aggregates where [Product] = [City] if you want.