How to make sure a PowerQuery pivot holds some pre defined values?

Question

My input is a table :

And I need to do a custom pivot where I make sure that :

• the columns are always five even if the column ID had for example only three and for the missing ones, we need to fill them with 0

• the columns have the following order :

  • 1 - TO START, 2 - IN PROGRESS, 3 - CANCELLED, 4 - STANDBY and 5 - FINISHED

My expected output is a table like below :

+------------+---------------+-------------+-----------+------------+
|   TO START |   IN PROGRESS |   CANCELLED |   STANDBY |   FINISHED |
|------------+---------------+-------------+-----------+------------|
|          6 |            13 |           1 |         0 |         14 |
+------------+---------------+-------------+-----------+------------+

But my code gives me this right now :

Can you guys show me how to do that ?

Here is the minimal reproducible code used to generate my example :

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8vRTCAjydw9yDQ5W0lEyUorVQRczBou5efp5Bnu4ugAFTMECzo5+zq4+PmARA7BIiL9CcIhjUAhcD7ISQ+xKkIzFZrcFuiJzpdhYAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, HOW_MANY = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"HOW_MANY", Int64.Type}}),
  Pivot = Table.Pivot(Types, List.Distinct(Types[ID]), "ID", "HOW_MANY", List.Sum)
in
  Pivot

Answer 1

UPDATE

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8vRTCAjydw9yDQ5W0lEyUorVQRczBou5efp5Bnu4ugAFTMECzo5+zq4+PmARA7BIiL9CcIhjUAhcD7ISQ+xKkIzFZrcFuiJzpdhYAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, HOW_MANY = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"HOW_MANY", Int64.Type}}),
    Dummy = #table({"ID", "HOW_MANY"},{ {"TO START",0},{"IN PROGRESS",0} ,{"CANCELLED",0},{"STANDBY",0},{"FINISHED",0}   }) & Types,
  Pivot = Table.Pivot(Dummy, List.Distinct(Dummy[ID]), "ID", "HOW_MANY", List.Sum)
in
  Pivot