How to cope with missing columns in Pivot

[ZLJ]

I have a query that pivots the data and then adds some subtotal columns by Summing the values in a few columns.

i.e. 

Job Number, cost type A, cost type B,, cost type C, cost type 1, cost type 2, sum A&B&C, sum 1&2, sum A&B&C&1&2

However, when one of the cost types is then missing from the pivot, as they dont have any costs that month, the Sum then errors.  Does anyone have a solution that will allow me to use Wildcards for columns names in a forumla, make the columns included in a formula optional, or add a column if it is not already there?  

Currently the only solution i can think of is to append the query to a blank query that has all the columns names in it already.... 

[v-shex-msft · Accepted solution]

Hi @ZLJ,

You can refer to below formula to check exist columns and add the column not exist:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\poivt table with miss type.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobName", type text}, {"Type", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum),
    #"Check Exist" =
let
    #"Check Column" =  if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
    #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
    #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
    #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
    #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
    #"Check Column4"
in
#"Check Exist"

base table:

 

After pivot operate:

 

After "check exist" function:

formula:

    #"Check Exist" =
let
    #"Check Column" =  if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
    #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
    #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
    #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
    #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
    #"Check Column4"

Regards,
Xiaoxin Sheng

[AntWickenden]

Hi Xiaoxin Sheng

I have a very similar issue to this, and I have tried plugging your solution into my report and adjusting the column headers accordingly... but I get a Token Comma Expected error which I can't seem to resolve...

The summary behind my report is that it is a tracker for order items being imported and each item has 1 of 5 statuses...as follows...

1: Awaiting Receipt at Origin

2: On Hand at Origin

3: In Transit to UK

4: Arrived in UK, Awaiting Clearance

5: Delivered to UK DC

As the order items move through the shipping process they move between these statuses in order.

This is not a continual report and is only to track items for 6 months of the year and as such, not all the 5 statuses will be used at a given time. hence the need for this...

Below is my code, with your code plugged in after the #"Pivoted Column" stage and before i reorder the columns etc.

let
Source = #"Article Quantity Lookup",
#"Reordered Columns" = Table.ReorderColumns(Source,{"Item No", "Item Desc", "Transit Status Overview", "PO Warehouse", "Item Qty Shipped", "ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"PO Warehouse"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Item No", "Transit Status Overview", "Item Desc", "Item Qty Shipped", "ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Transit Status Overview"]), "Transit Status Overview", "Item Qty Shipped", List.Sum),
#"Check Exist" =
let
#"Check Column" = if not Table.HasColumns(#"Pivoted Column","1: Awaiting Receipt at Origin”) then Table.AddColumn(#"Pivoted Column", "1: Awaiting Receipt at Origin",each null) else #"Pivoted Column",
#"Check Column1" = if not Table.HasColumns(#"Check Column","2: On Hand at Origin") then Table.AddColumn(#"Check Column", "2: On Hand at Origin",each null) else #"Check Column",
#"Check Column2" = if not Table.HasColumns(#"Check Column1","3: In Transit to UK") then Table.AddColumn(#"Check Column1", "3: In Transit to UK",each null) else #"Check Column1",
#"Check Column3" = if not Table.HasColumns(#"Check Column2","4: Arrived in UK, Awaiting Clearance") then Table.AddColumn(#"Check Column2", "4: Arrived in UK, Awaiting Clearance",each null) else #"Check Column2",
#"Check Column4" = if not Table.HasColumns(#"Check Column3","5: Delivered to UK DC") then Table.AddColumn(#"Check Column3", "5: Delivered to UK DC",each null) else #"Check Column3"
in
#"Check Column4"
in
#"Check Exist"

#"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",{"Item No", "Item Desc", "2: On Hand at Origin", "3: In Transit to UK", "4: Arrived in UK, Awaiting Clearance", "5: Delivered to UK DC"}),
#"Inserted Sum" = Table.AddColumn(#"Reordered Columns2", "Addition", each List.Sum({[#"2: On Hand at Origin"], [#"3: In Transit to UK"], [#"4: Arrived in UK, Awaiting Clearance"], [#"5: Delivered to UK DC"]})),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",{{"Addition", "Total Quantity"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Item No"}, #"Article Level Earliest Events", {"Item No"}, "Article Level Earliest Events", JoinKind.LeftOuter),
#"Expanded Article Level Earliest Events" = Table.ExpandTableColumn(#"Merged Queries", "Article Level Earliest Events", {"Earliest ETA", "Earliest ETA +10 days", "Earliest INDC", "Earliest Delivery"}, {"Earliest ETA", "Earliest ETA +10 days", "Earliest INDC", "Earliest Delivery"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Article Level Earliest Events", "Available in DC?", each if [Earliest Delivery] = null then "No" else "Yes"),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Item No", Order.Ascending}})
in
#"Sorted Rows"

I hope you can help with this as it is frustrating me.

Many thanks in advance

Anthony

[ZLJ]

Perfect, thanks @v-shex-msft will give that a try today!