Table.Pivot can pivot missing value in column - Power Query

Our source data includes columns for Product, Month, and Sales.

Sales occur in January, March, and April, meaning there are no sales recorded for February.

Pivot in PQ.xlsx

Download XLSX • 20KB

The goal is to pivot the Month data into columns for all months, ensuring that the final table contains a column for February with null values.

In this post, I will show a simple method using the Table.Pivot function to achieve this.

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = 

 [

 Lst = List.Transform(Source[Month], (f) => Date.Month(Date.FromText(f&"0"))),

 fin = Table.Pivot(Source,List.Transform({List.Min(Lst)..List.Max(Lst)}, (f) => Date.ToText(#date(2000,f,1),"MMM")),"Month","Sales", List.Sum)

 ][fin]

in

 Result