Forum Discussion
How to cope with missing columns in Pivot
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....
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
3 Replies
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 ShengPerfect, thanks Anonymous will give that a try today!
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
