Pivoting "not working" in PowerQuery

[campelliann]

So basically I am Pivoting a column called "budget type". That then transforms into "Margem" "Revenue" Custo (3 last columns).

The issue is that this only works if Projecto and subproject  Rows are equal. For instance. Project B has always Sub project y, so the 15 from "Margem" and "Revenue" is aligned. 

But for Project A, because subproject is different I get a null in the revenue first 4 rows. 

Is it possible to pivot only based on column Projecto?

1) Start (Before Pivoting)

2) After Pivoting (Project A should be like project B having 15-15 every row)

[nvprasad]

Hi Campelliann,

The transofmation looks correct only. There is no data for revenue with the combination of Projecto A & Subprojecto B. Hence it is showing null. 

You can replace null with 0 for last 3 columns ( after transformation).

Thank You,

Regads,

Venkat Nalla

[campelliann]

Hi there @nvprasad @ajbogle, is there away to do the Pivot only based on column Project (ignoring SubProject).

[ajbogle · Accepted solution]

You can try removing the column before the pivot. Technically you are unpivoting by the way.

Rows to Column = Unpivot

Column to Rows = Pivot

[ajbogle]

It actually looks correct. Look at Subprojecto, Projecto and Budget Type (before) and then (after). The reason for the null is that before you pivoted, those rows didn't have a value to begin with, therefore it captures the value as null.

(i.e.) Before you pivoted, Projecto A with Subprojecto B did not have a Budget Type of Custo; therefore Null 

Double check they should have a value of 15, if so, you need to fill it up/down. Click 'Transform' and look for this: