Forum Discussion

Advocate II

9 years ago

Solved

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, s...

Anonymous

9 years ago

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

check existed columns.zip46 KB

Anonymous

Not applicable

9 years ago

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:

Regards,
Xiaoxin Sheng

check existed columns.zip46 KB

ZLJ
Advocate II
9 years ago
Perfect, thanks Anonymous will give that a try today!
AntWickenden
Frequent Visitor
4 years ago
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

Forum Discussion

How to cope with missing columns in Pivot

Related content

Inferring Missing Columns and Imputing Missing Data

Power BI Pivot Columns ?

Pivot issue

Pivot / Unpivot?

Power Query Pivoting columns issue