Unpivoting gives the wrong row count in power query

Question

my initial data set is labeled "start", and I'm trying to reach the "end" stage :

I'm using excel's power query to do this. Essentially, the columns price end and price start need to be one column and the start and end points also need to be combined into one column. I hope the picture below is descriptive enough.

Using excel's powerquery, I've been able to unpivot the price columns and combine them as one column. When I do the same on the start and end columns, I get double the records (which makes sense the way the operation is done, but it's not what I need) - as shown under the title "powerquery". I still need only 24 records to show up. Is there another way to do this....doesn't seem like unpivoting will work, or maybe I need to have a different order of steps to do this.

Suggestions are deeply appreciated. thanks!

I think there are typos in your "what I need" table. Could you please correct them. And also edit your question to show the code your are using to get to the preceding table. — Ron Rosenfeld
@Ron - I'll put the code later sir, if you still want to see it. — Chet

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-26T02:44:40

This isn't really a pivoting/unpivoting exactly. You can solve it that way but it needs a couple of extra steps to match the start columns and end columns together.

Another way of looking at is as appending two subtables like this:

Table.Combine(
  {
    Table.FromColumns(
        {Source[date], Source[start], Source[price start]},
        {"date", "location", "price"}
    ),
    Table.FromColumns(
         {Source[date], Source[end], Source[price end]},
         {"date", "location", "price"}
     )
  }
)

This can be summarized as

Table.Combine({StartSubtable_green, EndSubtable_yellow})

where each subtable has been adjusted to share common column names date, location, and price.

Unpivoting gives the wrong row count in power query

1 Answers