Table Transformation

[cesarwheezer]

Hello everyone,

I am attempting to add four new column headers under the "Forecast Description" section and populate them with the corresponding field values. I have created an Excel table to illustrate what I am aiming to achieve. A screenshot of the dataset in the editor is included below.

[cesarwheezer]

Hello team,

I was able to use the existing dataset transformation to create my visuals. Appreicate all your help!

[scott2718]

Did you end up adding in an additional identifier to ensure you had the 4 pivoted fields correctly grouped? I don't think your solution was feasible without doing that, unless you aggregated somehow to reduce the dataset to only unique forecasted description category and work order ID combinations

[v-pgoloju · Accepted solution]

Hi @cesarwheezer,

Thank you for reaching out to the Microsoft Fabric Forum Community, and a special thanks to the @scott2718, @BA_Pete  and @Shubham_rai955  for their prompt and helpful responses.

Could you please provide a bit more information, as suggested by @BA_Pete , so we can assist you further?

Best regards,
Prasanna Kumar

[BA_Pete]

Hi @cesarwheezer ,

This looks like an XY Problem to me.

Can you give some detail as to WHY you want to restructure your table like this please?

It's currently in the optimal format for Power BI model reporting so, if you can explain what you're actually trying to achieve in the end, there may be a quicker/easier/more robust way to achieve that goal.

Pete

[Shubham_rai955]

Here is the simplest way to do this in Power Query.

You want:
Each Work Order ID will end up with four new columns, one for each Forecast Description.
Each column should show its matching Field Value.

Think of it like pivoting the data.

Do this:

1. Select the column Forecast Description.

2. Go to Transform, then Pivot Column.

3. For Values Column, choose Field Value.

4. For Advanced Options, choose Dont Aggregate.

Power Query will create four new columns automatically.
Each column will show the Field Value for that Forecast Description, exactly like your Excel example.

[scott2718]

Looks like you just need to use the Pivot transformation. 

Select Field Description as the field to pivot, and then in the aggregation options select Don't Aggregate.

[cesarwheezer]

Not quite. I tried the pivot column function. The field values are not listed the way I want it. At the end of thread, I attached a screenshot of exactly what I want to see as the final transformation. 

[scott2718]

I think your data is not good. I can see that your work order ID = 1 has multiple dates for adjusted forecast. But since the value field is in the same column instead of next to it, you can't guarantee you will select the right pair of values if you try to unpivot it.

Do you have another data source that has dates and dollars in adjacent fields? Or an extra field that provides another ID? Without a way to make each date/dollar pair unique, you probably won't accurately pivot this.